Question

BigQuery + Looker: Timestamp vs Datetime

  • 23 February 2021
  • 2 replies
  • 26 views

Hello, we have Looker on top of BigQuery. Currently there is a lot of mixed usage when it comes to dates and times. We’re doing some cleaning and decided that we should all try to use the same format whenever possible, to avoid converting and extracting back and forth all the time. 

No one in our team really have a strong opinion on the matter, so I’m wondering if there are any best practice recommendations? FYI we don’t operate across different time zones.

So if you recommend that we use date format X, we will try to convert all dates to format X as early as possible in the flow, and all comparisons and joins will use format X. 

Looking forward to your recommendations and tips for further reading


2 replies

Userlevel 3

Hi Olof,

 

I am by no means an authority when it comes to formatting, but I always recommend using timestamps as a rule of thumb. This seems to be the consensus, according to StackOverflow.

 

I think there are very limited use cases for datetime formats.

 

Would love to hear other opinions though!

 

Best,

Userlevel 6
Badge

Timestamp has the limitation of supporting only 17 extra years up till 2038. Datetime seems problematic unless you include an extra step of storing datetime in agreed time zone. Let’s say UTC.

 

I tend to use DATETIME in my MySQL projects because most of the data I keep is related to a calendar. Sometimes I still keep created_at/updated_at as timestamps but there’s another way to keep away from the 2038 timestamp limit. You can use BIGINT/INT64 column and simply store milliseconds since epoch. 

 

There are many ways to go about it and it depends what data you store, how you want to represent it, usage of time zones now, possible future usage of time zones, and whether 2038 limit is something that can affect you.

 

I particularly follow this:

 

Reply