Postgres Pro
Downloads
Home   >   mailing lists

Thread: Timestamp with timezone output

Timestamp with timezone output

From
Arnaud Inovia Team
Date:
I have some question regarding some timezone behaviour.

Documentation is saying:

> When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

While using "psql", when selecting a column timestamp with timezone, I get results with different timezones:

-[ RECORD 6 ]---+-----------------------
expiration_date | 2015-09-07 00:00:00+02
-[ RECORD 7 ]---+-----------------------
expiration_date | 2015-11-27 00:00:00+01

Shouldn't all value be converted to the same timezone ?

Thank you for your help !

--
Arnaud Becher
Paris - San Francisco
10 rue du Faubourg Poissonnière - 75010 Paris
T. + 33 (0)6 17 15 52 43
http://www.inovia.fr
  
Attachment

Re: Timestamp with timezone output

From
Larry Rosenman
Date:

On 2016-05-25 09:47, Arnaud Inovia Team wrote:

I have some question regarding some timezone behaviour.
 
Documentation is saying:
 
> When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).
 
While using "psql", when selecting a column timestamp with timezone, I get results with different timezones:
 
-[ RECORD 6 ]---+-----------------------
expiration_date | 2015-09-07 00:00:00+02
-[ RECORD 7 ]---+-----------------------
expiration_date | 2015-11-27 00:00:00+01
 
Shouldn't all value be converted to the same timezone ?
 
 
 
Thank you for your help !

 

Daylight Savings Time?

 

-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

Re: Timestamp with timezone output

From
Guillaume Lelarge
Date:

Hi,

Le 25 mai 2016 4:50 PM, "Arnaud Inovia Team" <arnaud.becher@inovia-team.com> a écrit :
>
> I have some question regarding some timezone behaviour.
>
> Documentation is saying:
>
> > When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).
>
> While using "psql", when selecting a column timestamp with timezone, I get results with different timezones:
>
> -[ RECORD 6 ]---+-----------------------
> expiration_date | 2015-09-07 00:00:00+02
> -[ RECORD 7 ]---+-----------------------
> expiration_date | 2015-11-27 00:00:00+01
>
>
> Shouldn't all value be converted to the same timezone ?
>

Are you sure this column is a timestamp? Can you show us the table definition?

Re: Timestamp with timezone output

From
Kevin Grittner
Date:
On Wed, May 25, 2016 at 9:47 AM, Arnaud Inovia Team
<arnaud.becher@inovia-team.com> wrote:

> While using "psql", when selecting a column timestamp with
> timezone, I get results with different timezones:
>
> -[ RECORD 6 ]---+-----------------------
> expiration_date | 2015-09-07 00:00:00+02
> -[ RECORD 7 ]---+-----------------------
> expiration_date | 2015-11-27 00:00:00+01
>
> Shouldn't all value be converted to the same timezone ?

Perhaps your local time zone ends Daylight Saving Time between
those dates, so the offset from UTC is different on those dates?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Timestamp with timezone output

From
Arnaud Inovia Team
Date:
Ok, it seems the Daylight Savings Time is perfect explanation to me. Thanks y'all, you're the best !

--
Arnaud Becher
Paris - San Francisco
10 rue du Faubourg Poissonnière - 75010 Paris
T. + 33 (0)6 17 15 52 43
http://www.inovia.fr
  

On Wed, May 25, 2016 at 5:01 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
On Wed, May 25, 2016 at 9:47 AM, Arnaud Inovia Team
<arnaud.becher@inovia-team.com> wrote:

> While using "psql", when selecting a column timestamp with
> timezone, I get results with different timezones:
>
> -[ RECORD 6 ]---+-----------------------
> expiration_date | 2015-09-07 00:00:00+02
> -[ RECORD 7 ]---+-----------------------
> expiration_date | 2015-11-27 00:00:00+01
>
> Shouldn't all value be converted to the same timezone ?

Perhaps your local time zone ends Daylight Saving Time between
those dates, so the offset from UTC is different on those dates?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Attachment