Thread: Is there away to output a time stamp in a specified time zone with the time zone indicator (e.g. EDT)

On 05/02/2012 03:59 PM, Matrix Guy wrote:
> I'm referring to US time zones here. I'd like to output a time stamp
> field in a specified time zone. I'd also like to display the time zone
> (e.g. EDT) at the end. Ideally, this output needs to adjust the numeric
> portions of the stamp for standard time vs daylight time and then
> display EST or EDT accordingly. The switch between standard time and
> daylight time occurred on 3-11-2012. So time stamps before 3-11-2012
> should have EST at the end and those after should have EDT.
>
> to_char(my_timestamp_field , 'MM/DD/YYYY HH12:MI AM TZ') will output:
> 03/12/2012 10:23 AM EDT
> to_char(my_timestamp_fieldat time zone 'cdt', 'MM/DD/YYYY HH12:MI AM
> TZ') will only output 03/12/2012 09:23 AM
>
> Because we'll be reporting events in multiple US time zones, we must let
> users know which time zone the output timestamp is in.

How are you storing the timestamps? In other words in a timestamp with
timezone field or not?

>
> Thanks,
>
> Alex


--
Adrian Klaver
adrian.klaver@gmail.com

On 2012-05-02, Matrix Guy <matrixguy696@yahoo.com> wrote:

>
> I'm referring to US time zones here.=A0 I'd like to output a time stamp field
> in a specified time zone.=A0 I'd also like to display the time zone
(e.g. EDT) at the end.=A0 Ideally,

trivially this is done by issuing set commands

set session timezone to 'america/new_york';
set session datestyle to 'sql,mdy';

select now();
 05/12/2012 22:54:22.693175 EDT

select now() - interval '3 month' ;
 02/12/2012 22:56:38.28168 EST

set session timezone to 'america/denver';
select now();
 05/12/2012 21:00:13.998272 MDT


--
⚂⚃ 100% natural