DATETIME vs TIMESTAMP in BigQuery

DATETIME and TIMESTAMP in BigQuery are not the same and should not be used interchangeably!

One thing I encounter from time to time is mixing of DATETIME and TIMESTAMP types. Even casually converting TIMESTAMP(DATETIME_COLUMN) with no timezone provided.

This should not be done and you will get a type mismatch error when you, for example, try to compare them, for a very good reason.

What's the difference?

➡ DATETIME is a local time, happening once per day across the globe, at different points in time - it's 17:00 on January 12 first in Tokyo, then Bangalore, London and finally Los Angeles.

➡ TIMESTAMP is an absolute point in time and uses the UTC as a reference.

You can of course convert between the two, but you will NEED to provide a timezone context:
- if starting with a DATETIME, you need to provide a source timezone for the TIMESTAMP to be computed
- if you have a TIMESTAMP, you need to provide a target timezone for the DATETIME to be computed.

See below an illustration of how it's done.

SELECT

CURRENT_DATETIME('Europe/Bucharest') AS local_time_bucharest,

CURRENT_DATETIME('America/New_York') AS local_time_new_york,

CURRENT_TIMESTAMP() AS utc_timestamp,

TIMESTAMP(CURRENT_DATETIME('America/New_York'),'America/New_York') AS
timestamp_converted_from_datetime,

DATETIME( CURRENT_TIMESTAMP(), 'Europe/Bucharest') AS bucharest_datetime_from_timestamp