katcode

Use the gcloud bq cli tool 1) export the source table schema to json file. Each item only requires name and type fields. bq show --schema --format=prettyjson projectId:Dataset.table > emailschema.json https://cloud.google.com/bigquery/docs/schemas#specify-schema-manual-cli 2) Create the table in destination as partitioned bq mk --table --schema ./emailschema.json --time_partitioning_type DAY projectId:dataset.table https://cloud.google.com/bigquery/docs/creating-column-partitions

const moment = require('moment-timezone'); Calculating duration in seconds from current time to a future time. e.g. the next day const nextDayDateString = moment().tz('America/Los_Angeles').add(1, "days").format("YYYY-MM-DD 00:00:00"); const nextDayDate = moment(nextDayDateString).tz('America/Los_Angeles'); const nextDayExpireSeconds = Math.round(moment.duration(nextDayDate.diff(moment())).asSeconds()); Calculating unix timestamp of certain date in local time const nextDayDt = moment().tz('America/Los_Angeles').add(1, "days").format("YYYY-MM-DD 00:00:00"); const nextDayDtUnix = moment(nextDayDt).tz('America/Los_Angeles').utc().unix(); Determining date string by the number of days back using local time // Will display date string 30 days back const daysBack = 30; var dateFrom = moment.tz(moment().subtract(daysBack,'days').format('YYYY-MM-DD 00:00:00'), 'America/Los_Angele...

Use decorator and extract data for specific date adjusted for local time SELECT site, event_type, count(*) as total_events, EXACT_COUNT_DISTINCT(event_message_id) as uniq_events FROM [email.sendgrid_events@1471590000000-] WHERE event_provider = 'ongage' AND DATE(DATE_ADD(SEC_TO_TIMESTAMP(time_unix), -7, 'HOUR')) = '2016-08-21' AND site = 'careeralerts.com' GROUP BY site, event_type ORDER BY site, event_type Generating date as local time using moment.js const moment = require('moment-timezone'); // will be -420 minutes which is 7 hours var timezoneOffsetMinutes = moment.tz.zone('America/Los_Angeles').parse(moment()) * -1; SELECT siteId as site, type as campaignType, DATE(DATE_ADD(e.ts, timezoneOffsetMinutes, "minute")) as aggDate ... Get total counts grouped by the hour adjusted for PST time for the past 10 days SELECT hour(ts)-7,count(*) FROM [project:DataSet.SomeTable@-864000000-] WHERE   plat...