`Manipulating date time and micro timestamps in Google Cloud BigQuery`

Thu, 22 November 2018

bigquery

sql

example.sql

-- More information available here: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract

SELECT 
  UNIX_MICROS(TIMESTAMP "2018-11-08 00:00:00.000000 UTC") AS DateTime_To_Micro_TS,
  REPLACE("2018-11-08", "-", "") AS Date_To_Table,
  EXTRACT(YEAR FROM TIMESTAMP_MICROS(1541635200000000)) AS Year_From_Micro_Secs,
  EXTRACT(MONTH FROM TIMESTAMP_MICROS(1541635200000000)) AS Month_From_Micro_Secs,
  EXTRACT(DAY FROM TIMESTAMP_MICROS(1541635200000000)) AS Day_From_Micro_Secs,
  EXTRACT(HOUR FROM TIMESTAMP_MICROS(1541635200000000)) AS Hour_From_Micro_Secs,
  EXTRACT(MINUTE FROM TIMESTAMP_MICROS(1541635200000000)) AS Minute_From_Micro_Secs,
  EXTRACT(SECOND FROM TIMESTAMP_MICROS(1541635200000000)) AS Seconds_From_Micro_Secs,
  EXTRACT(DATE FROM TIMESTAMP_MICROS(1541635200000000)) AS YMD_Date

output.txt

DateTime_To_Micro_TS,Date_To_Table,Year_From_Micro_Secs,Month_From_Micro_Secs,Day_From_Micro_Secs,Hour_From_Micro_Secs,Minute_From_Micro_Secs,Seconds_From_Micro_Secs,YMD_Date
1541635200000000,20181108,2018,11,8,0,0,0,2018-11-08

bigquery

sql