Search code examples
google-bigquery

Timestamp showing a date 12 years in the future

Here is my code:

select 

  event_date,
 
  DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Karachi") as datetime_tstz
from 
`app.analytics_317927526.events_intraday_*`
where
event_timestamp=(select max(event_timestamp) from `app.analytics_317927526.events_intraday_*`

)

So the event_date is showing 20240318. And the datetime_tstz is showing 2036-06-03T07:06:23.005627.

Please note, I have disabled the cached results as well.

Solution

  • It's likely your data/query problem.

    Check this out: select unix_micros(TIMESTAMP "2036-06-03T07:06:23.005627 Asia/Karachi") => 2096089583005627

    If your datetime_tstz is resulting into "2036-06-03T07:06:23.005627" then its likely comes from your subquery output. Your subquery produces max value of event_timestamp in micros.

    (If your source data is in UTC you have to check for UTC version of your micros output value with your source data.)

    See this: when everything is in sync it just works:

    select 
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp_in_macros), "Asia/Karachi")) as event_date, 
DATETIME(TIMESTAMP_MICROS(event_timestamp_in_macros), "Asia/Karachi") as datetime_tstz
from 
(select unix_micros(TIMESTAMP "2036-06-03T07:06:23.005627 Asia/Karachi") as event_timestamp_in_macros)

    Another thing to check if your event_date and event_timestamp are connected or not. If event_date is not derived from event_timestamp, then its another data issue.