CODEWITHSUNDEEP
sqlgoogle-bigquery
poemofxtasy
poemofxtasy

Reputation: 9

Converting String to Timestamp

I have two columns. One which is a UTC timestamp. The second column is the UTC offset formatted as a string. So California is -08:00, and Paris would be 01:00.

How can I combine the two columns to get local time? When I try to cast the string to timestamp I get an invalid timestamp due to the negatives.

Upvotes: 0

Views: 1585

Answers (3)

Nizam Arusada
Nizam Arusada

Reputation: 11

What I always do, I use condition to convert the timestamp UTC into local datetime. I check the list of tz name in here. https://en.wikipedia.org/wiki/List_of_tz_database_time_zones. As long you have utc_offset or region or country name you can change/convert UTC to local timestamp / datetime.

Below the query:

#standardSQL
WITH
  sample_data_utc AS (
  SELECT
    TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), SECOND) AS utc_timestamp,
    '01:00' AS utc_offset
  UNION ALL
  SELECT
    TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), SECOND) AS utc_timestamp,
    '-08:00' AS utc_offset)
SELECT
  STEP_ONE.utc_timestamp,
  CASE STEP_ONE.utc_offset
    WHEN '01:00'  THEN DATETIME(STEP_ONE.utc_timestamp, "Europe/Paris")
    WHEN '-08:00' THEN DATETIME(STEP_ONE.utc_timestamp, "America/Tijuana")
  --find out the TZ name in here https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
  ELSE
  NULL
END
  AS local_timestamp
FROM
  sample_data_utc AS STEP_ONE

with output:

Row     utc_timestamp               local_timestamp  
1       2020-03-01 03:41:27 UTC     2020-02-29T19:41:27  
2       2020-03-01 03:41:27 UTC     2020-03-01T04:41:27

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below example for BigQuery 

#standardSQL
WITH `project.dataset.yourtable` AS (
  SELECT '-08:00' timezone_offset, TIMESTAMP '2020-03-01 03:41:27 UTC' UTC_timestamp UNION ALL
  SELECT '01:00',  '2020-03-01 03:41:27 UTC'
)
SELECT * EXCEPT(hour, minute), 
  DATETIME(TIMESTAMP_ADD(TIMESTAMP_ADD(UTC_timestamp, INTERVAL CAST(hour AS INT64) HOUR), INTERVAL CAST(minute AS INT64) MINUTE)) AS local_time
FROM `project.dataset.yourtable`,
UNNEST([STRUCT(SPLIT(timezone_offset, ':')[OFFSET(0)] AS hour, SPLIT(timezone_offset, ':')[OFFSET(1)] AS minute)])

with output 

Row     timezone_offset     UTC_timestamp               local_time   
1       -08:00              2020-03-01 03:41:27 UTC     2020-02-29T19:41:27  
2       01:00               2020-03-01 03:41:27 UTC     2020-03-01T04:41:27

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can convert a string representation of a timestamp into a timestamp using timestamp().

So, this should work:

select timestamp('2020-01-01 00:00:00-08:00') as ca_time,
       timestamp('2020-01-01 00:00:00+01:00') as paris_time

If you want local time, you can convert to a datetime:

select datetime(timestamp('2020-01-01 00:00:00-08:00'), 'America/New_York') as ca_time,
       datetime(timestamp('2020-01-01 00:00:00+01:00'), 'America/New_York') as paris_time

That said, I would encourage you to convert the strings to timestamps and leave them there. Just convert to local time for output purposes and to determine the local date. Learning to work with UTC has a bit of a learning curve, but it prevents lots of problems down the road.

Upvotes: 1

Related Questions