Video 2 #etl- How to Create DateTime in BigQuery. #datetime Vs. #timestamps

How to reconstruct DateTIme in BigQuery. What is the main difference between DateTime and TimeStamp?

In this video, we take the date and time from video 1 to reconstruct DateTime. Then we have a whole chapter dedicated to explaining the difference between DateTime and TimeStamp.

Queries used in the video:

(1) Table Creation Statement Containing Unstructured Dates:
Replace the dataset name if required in your query
=======

AS
SELECT 'sugar' as item, '2022-09-03 18:34:23' as deliveryTime
UNION ALL SELECT 'flour', '2022-09-02 14:24:24'
UNION ALL SELECT 'tea', '08/03/2022 14:20:21'
UNION ALL SELECT 'butter', '8/03/2022 14:15:20'
UNION ALL SELECT 'cocoa', '7/03/2022 11:14:20'
UNION ALL SELECT 'essence', '2022-09-01 10:11:09'
UNION ALL SELECT 'cherries', '2022-09-01 08:11:09'
UNION ALL SELECT 'tutti frutti', '9/01/2022 11:14:20'
========

(2) Regular Expression for Matching and Extracting Unstructured Dates and Time

=======
SELECT *,
CASE
WHEN REGEXP_CONTAINS(deliveryTime, r'[0-9]{4}\-[0-9]{2}\-[0-9]{2}') THEN PARSE_DATE('%F',SUBSTR(deliveryTime,1,10))
WHEN REGEXP_CONTAINS(deliveryTime, r'[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}[\s]?') THEN PARSE_DATE("%m/%d/%Y", REGEXP_EXTRACT(deliveryTime, r'[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}[\s]?') )
END as persed_date,

PARSE_TIME("%T", REGEXP_EXTRACT(deliveryTIme, r'[\s][\d]+\:[\d]+\:[\d]+$')) AS persed_time
==========

Other Resources

Regular Expressions in BigQuery:

Date Functions in BigQuery:

DateTIme Resources: