Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #95 > unrolled thread

timestamp and time zone

Back to article view | Back to comp.databases.postgresql

Contents

  timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-28 18:00 +0200
    Re: timestamp and time zone Dog is love <michaelnewportyahoo.com@gmail.com> - 2011-04-28 10:05 -0700
      Re: timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-28 19:22 +0200
    Re: timestamp and time zone Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2011-04-29 08:45 +0200
      Re: timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-29 12:23 +0200
        Re: timestamp and time zone D Yuniskis <not.going.to.be@seen.com> - 2011-04-29 06:02 -0700
    Re: timestamp and time zone Jasen Betts <jasen@xnet.co.nz> - 2011-04-30 11:15 +0000
      Re: timestamp and time zone Jasen Betts <jasen@xnet.co.nz> - 2011-04-30 11:26 +0000
      Re: timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-30 17:58 +0200
        Re: timestamp and time zone Jasen Betts <jasen@xnet.co.nz> - 2011-05-01 11:33 +0000

#95 — timestamp and time zone

Hi,

I did not quite understand the use case of timestamp with time zone.

When do you need 'with time zone'? My tests show you can
calculate other time zones time easily from a timestamp without
time zone.

Is timestamp internally stored normalized to utc, and calculated
to client time zone? Or to server time zone?

Suppose a linux server with system clock set to utc.

thanks for the light you will certainly shed on this...


/Str.

[toc] | [next] | [standalone]

#96

On 28 Apr., 18:00, "M. Strobel" <sorry_no_mail_h...@nowhere.dee>
wrote:
> Hi,
>
> I did not quite understand the use case of timestamp with time zone.
>
> When do you need 'with time zone'? My tests show you can
> calculate other time zones time easily from a timestamp without
> time zone.
>
> Is timestamp internally stored normalized to utc, and calculated
> to client time zone? Or to server time zone?
>
> Suppose a linux server with system clock set to utc.
>
> thanks for the light you will certainly shed on this...
>
> /Str.

some light...

http://en.wikipedia.org/wiki/Tz_database

[toc] | [prev] | [next] | [standalone]

#97

Am 28.04.2011 19:05, schrieb Dog is love:
> On 28 Apr., 18:00, "M. Strobel" <sorry_no_mail_h...@nowhere.dee>
> wrote:
>> Hi,
>>
>> I did not quite understand the use case of timestamp with time zone.
>>
>> When do you need 'with time zone'? My tests show you can
>> calculate other time zones time easily from a timestamp without
>> time zone.
>>
>> Is timestamp internally stored normalized to utc, and calculated
>> to client time zone? Or to server time zone?
>>
>> Suppose a linux server with system clock set to utc.
>>
>> thanks for the light you will certainly shed on this...
>>
>> /Str.
> 
> some light...
> 
> http://en.wikipedia.org/wiki/Tz_database

I've seen this light before.

The question is rather: how is timestamp stored? If it is
normalized to utc then what is the use of time zone?

/Str.

[toc] | [prev] | [next] | [standalone]

#98

M. Strobel, 28.04.2011 18:00:
> I did not quite understand the use case of timestamp with time zone.
>
> When do you need 'with time zone'? My tests show you can
> calculate other time zones time easily from a timestamp without
> time zone.
>
> Is timestamp internally stored normalized to utc, and calculated
> to client time zone? Or to server time zone?

 From the manual at:

http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

[toc] | [prev] | [next] | [standalone]

#99

Am 29.04.2011 08:45, schrieb Harry Tuttle:
> M. Strobel, 28.04.2011 18:00:
>> I did not quite understand the use case of timestamp with time
>> zone.
>>
>> When do you need 'with time zone'? My tests show you can
>> calculate other time zones time easily from a timestamp without
>> time zone.
>>
>> Is timestamp internally stored normalized to utc, and calculated
>> to client time zone? Or to server time zone?
> 
> From the manual at:
> 
> http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES
> 
> 
> All timezone-aware dates and times are stored internally in UTC.
> They are converted to local time in the zone specified by the
> timezone configuration parameter before being displayed to the
> client.
> 
> 

And I can _not_ conclude from this that a date/time without
timezone is not stored in utc, right?

The server clock is set to utc.

So where does the difference show up?

/Str.

[toc] | [prev] | [next] | [standalone]

#100

On 4/29/2011 3:23 AM, M. Strobel wrote:
> Am 29.04.2011 08:45, schrieb Harry Tuttle:
>> M. Strobel, 28.04.2011 18:00:
>>> I did not quite understand the use case of timestamp with time
>>> zone.
>>>
>>> When do you need 'with time zone'? My tests show you can
>>> calculate other time zones time easily from a timestamp without
>>> time zone.
>>>
>>> Is timestamp internally stored normalized to utc, and calculated
>>> to client time zone? Or to server time zone?
>>
>>  From the manual at:
>>
>> http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES
>>
>>
>> All timezone-aware dates and times are stored internally in UTC.
>> They are converted to local time in the zone specified by the
>> timezone configuration parameter before being displayed to the
>> client.
>
> And I can _not_ conclude from this that a date/time without
> timezone is not stored in utc, right?
>
> The server clock is set to utc.
>
> So where does the difference show up?

On Input:

TIMESTAMP '2004-10-19 10:23:54'
TIMESTAMP '2004-10-19 10:23:54+02'

"PostgreSQL never examines the content of a literal string before
determining its type, and therefore will treat both of the above as
timestamp without time zone. To ensure that a literal is treated as
timestamp with time zone, give it the correct explicit type.

"In a literal that has been determined to be timestamp without time
zone, PostgreSQL will silently ignore any time zone indication. That
is, the resulting value is derived from the date/time fields in the
input value, and is not adjusted for time zone."

On Output:

"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."

[toc] | [prev] | [next] | [standalone]

#102

On 2011-04-28, M. Strobel <sorry_no_mail_here@nowhere.dee> wrote:
> Hi,
>
> I did not quite understand the use case of timestamp with time zone.
>
> When do you need 'with time zone'?

it's best to use it when you are dealing with real events that happen
at a specific instant

> My tests show you can
> calculate other time zones time easily from a timestamp without
> time zone.

> Is timestamp internally stored normalized to utc, and calculated
> to client time zone? Or to server time zone?

timestamp with timezone is internally stored as a UTC timestamp
no zone information is stored. So '2011-04-30 23:02:39.296282+12'
is actually stored as '2011-04-30 11:02:39.296282 UTC'.  (I know 
it says "with timezone" on the label - postgresql fakes it )
(actually it's stored as a number, not as a string)

When it's retreived it will be translated to the best guess zone
apropriate for the user (using the server operating system's timezone
database)

timestamp is internally stored with no zone translation so 
'2011-04-30 23:02:39.296282' will look the same whatever 
the user uses for their timezone. (different datestyle
settings can effect the date part however)

-- 
⚂⚃ 100% natural

[toc] | [prev] | [next] | [standalone]

#103

On 2011-04-30, Jasen Betts <jasen@xnet.co.nz> wrote:

> timestamp is internally stored with no zone translation so 
> '2011-04-30 23:02:39.296282' will look the same whatever 
> the user uses for their timezone. (different datestyle
> settings can effect the date part however)

It is again stored internally as a number. 

 As I understand it: this number is the difference in 
milliseconds between "1970-01-01 00:00:00" and the time
given, for this computation both times are expressed in 
a region that does not do daylight saving or leap seconds 
(eg: UTC).  Most linux systems use a 64 bit integer, but
some oddball ones may use a 64 bit floating point number
instead.


-- 
⚂⚃ 100% natural

[toc] | [prev] | [next] | [standalone]

#104

Am 30.04.2011 13:15, schrieb Jasen Betts:
> On 2011-04-28, M. Strobel <sorry_no_mail_here@nowhere.dee> wrote:
>> Hi,
>>
>> I did not quite understand the use case of timestamp with time zone.
>>
>> When do you need 'with time zone'?
> 
> it's best to use it when you are dealing with real events that happen
> at a specific instant
> 
>> My tests show you can
>> calculate other time zones time easily from a timestamp without
>> time zone.
> 
>> Is timestamp internally stored normalized to utc, and calculated
>> to client time zone? Or to server time zone?
> 
> timestamp with timezone is internally stored as a UTC timestamp
> no zone information is stored. So '2011-04-30 23:02:39.296282+12'
> is actually stored as '2011-04-30 11:02:39.296282 UTC'.  (I know 
> it says "with timezone" on the label - postgresql fakes it )
> (actually it's stored as a number, not as a string)
> 
> When it's retreived it will be translated to the best guess zone
> apropriate for the user (using the server operating system's timezone
> database)
> 
> timestamp is internally stored with no zone translation so 
> '2011-04-30 23:02:39.296282' will look the same whatever 
> the user uses for their timezone. (different datestyle
> settings can effect the date part however)
> 
I see. So there is no automatic translation 'without time zone',
but if you ask the system to translate it to another time zone it
does so assuming local time.

Thanks, I thought it was too much work to set up tests within
different time zones...

/Str.

[toc] | [prev] | [next] | [standalone]

#106

On 2011-04-30, M. Strobel <sorry_no_mail_here@nowhere.dee> wrote:

> I see. So there is no automatic translation 'without time zone',
> but if you ask the system to translate it to another time zone it
> does so assuming local time.
>
> Thanks, I thought it was too much work to set up tests within
> different time zones...

you can temporarily change the timezone that postres considers local 
to you with the 

"set timezone to " command 
eg:

  set timezone to "Asia/Singapore"; 
  select now();

this makes testing other zones easy.


-- 
⚂⚃ 100% natural

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web