w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to handle time zone issue without storing date time as TIMESTAMP WITH TIME ZONE

If you have the names of the time zones, you can use a query similar to the following to adjust alter timestamps:

SELECT
  e.last_updated_date AS cst_last_updated_date,
  (FROM_TZ(e.last_updated_date, 'US/Central') AT TIME ZONE 'US/Eastern') AS
est_last_updated_date
FROM events_tbl e;

In my experience this will automatically account for DST based on the locale.

If you're working with DATES and not TIMESTAMPS, you can also do the following:

select 
  CAST (e.last_updated_date AS TIMESTAMP) AS cst_last_updated_date,
  (FROM_TZ(CAST (e.last_updated_date AS TIMESTAMP), 'US/Central') AT TIME
ZONE 'US/Eastern') AS est_last_updated_date
from events_tbl e;




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.