UTC to Local Date Time Conversion

[Josh]

Hi guys,

Is this a bug? I'm trying to convert a UTC date to my local time zone (EST).

The getdate() function returns the correct date and time, and the getutcdate() function returns the correct UTC date and time, but when I use the AT TIME ZONE feature to convert the UTC time to EST (my local time zone) and cast the result to date time the result is one hour off...

Code: Select all

dbadmin=> show time zone;
   name   |  setting
----------+------------
 timezone | US/Eastern
(1 row)

Code: Select all

dbadmin=> SELECT getdate(), getutcdate(), cast(getutcdate() at time zone 'EST' as datetime) utc_to_est;
          getdate           |         getutcdate         |         utc_to_est
----------------------------+----------------------------+----------------------------
 2012-10-04 10:19:36.827054 | 2012-10-04 14:19:36.827054 | 2012-10-04 15:19:36.827054
(1 row)

Time: First fetch (1 row): 7.747 ms. All rows formatted: 7.781 ms

Is this because of Daylight Saving Time?

I'm confused.

Thanks!

[Josh]

Ok, I figured out I should use EDT as the time zone to indicate I want to include Daylight Saving Time:

Code: Select all

dbadmin=> select getdate(), getutcdate(), getutcdate() at time zone 'EDT', getutcdate() at time zone 'EDT'::TIMESTAMP;
          getdate           |         getutcdate         |           timezone            |          timezone
----------------------------+----------------------------+-------------------------------+----------------------------
 2012-10-04 12:45:09.007454 | 2012-10-04 16:45:09.007454 | 2012-10-04 16:45:09.007454-04 | 2012-10-04 16:45:09.007454
(1 row)

However, I still have the question as to why the result in the 4th column is 2012-10-04 16:45:09.007454 and not 2012-10-04 12:45:09.007454?!?!

Thanks for any help on this...

[JimKnicely]

Hi,

Use the NEW_TIME function to convert time zones!

Code: Select all

dbadmin=> select getdate(), new_time(getutcdate(), 'UTC', 'EDT');
          getdate           |          new_time
----------------------------+----------------------------
 2012-10-05 08:52:44.948551 | 2012-10-05 08:52:44.948551
(1 row)