Postgres Pro
Downloads
Home   >   mailing lists

Thread: TimestampTZ

TimestampTZ

From
Naz Gassiep
Date:
When entering data into a timestamptz field, if no timezone is added
does it assume you've entered a UTC time, or the time at the timezone
set in the session with SET TIMEZONE, or the local system time ?
- Naz

Re: TimestampTZ

From
Michael Glaesemann
Date:
On Aug 12, 2007, at 23:47 , Naz Gassiep wrote:

> When entering data into a timestamptz field, if no timezone is
> added does it assume you've entered a UTC time, or the time at the
> timezone set in the session with SET TIMEZONE, or the local system
> time ?


As clearly stated in the documentation

http://www.postgresql.org/docs/8.2/interactive/datatype-
datetime.html#DATATYPE-TIMEZONES

> An input value that has an explicit time zone specified is
> converted to UTC using the appropriate offset for that time zone.
> If no time zone is stated in the input string, then it is assumed
> to be in the time zone indicated by the system's timezone
> parameter, and is converted to UTC using the offset for the
> timezone zone.


Michael Glaesemann
grzm seespotcode net

Re: TimestampTZ

From
hubert depesz lubaczewski
Date:
On Mon, Aug 13, 2007 at 02:47:06PM +1000, Naz Gassiep wrote:
> When entering data into a timestamptz field, if no timezone is added
> does it assume you've entered a UTC time, or the time at the timezone
> set in the session with SET TIMEZONE, or the local system time ?

i dont understand - why didn't you simply test? instead of writing email
you could simply:
# select now();
              now
-------------------------------
 2007-08-13 07:13:54.363458+02
(1 row)

# select '2007-08-13 07:13:54'::timestamptz;
      timestamptz
------------------------
 2007-08-13 07:13:54+02
(1 row)

and everything is clear - it's local timezone.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Re: TimestampTZ

From
Naz Gassiep
Date:
> As clearly stated in the documentation
>
> http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-TIMEZONES
>

Perhaps I'm thick, but I don't find that particular page to be clear on
this at all.
- Naz.

Re: TimestampTZ

From
Paul Lambert
Date:
Naz Gassiep wrote:
>
>> As clearly stated in the documentation
>>
>> http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-TIMEZONES
>>
>
> Perhaps I'm thick, but I don't find that particular page to be clear on
> this at all.
> - Naz.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

Refer to this paragraph: (8.5.1.2)

For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
Time, GMT). An input value that has an explicit time zone specified is
converted to UTC using the appropriate offset for that time zone. If no
time zone is stated in the input string, then it is assumed to be in the
time zone indicated by the system's timezone parameter, and is converted
to UTC using the offset for the timezone zone.


--
Paul Lambert
Database Administrator
AutoLedgers

Re: TimestampTZ

From
Paul Lambert
Date:
Paul Lambert wrote:
> Refer to this paragraph: (8.5.1.2)
>
> For timestamp with time zone, the internally stored value is always in
> UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
> Time, GMT). An input value that has an explicit time zone specified is
> converted to UTC using the appropriate offset for that time zone. If no
> time zone is stated in the input string, then it is assumed to be in the
> time zone indicated by the system's timezone parameter, and is converted
> to UTC using the offset for the timezone zone.
>
>

Chapter 8.5.1.3 that was actaully, my apologies.

--
Paul Lambert
Database Administrator
AutoLedgers

Re: TimestampTZ

From
Michael Glaesemann
Date:
On Aug 13, 2007, at 0:35 , Naz Gassiep wrote:

>
>> As clearly stated in the documentation
>>
>> http://www.postgresql.org/docs/8.2/interactive/datatype-
>> datetime.html#DATATYPE-TIMEZONES
>
> Perhaps I'm thick, but I don't find that particular page to be
> clear on this at all.

Had you read the documentation before you posted? Did you read the
part that was quoted to you by both myself and Paul Lambert? If so,
why didn't you mention this in your first post? While the page has a
lot of information on it, the part that deals with time zones on
input is pretty clear and plainly stated. What do you find confusing
about it? If you can explain how part of the documentation is not
clear, then perhaps the documentation can be improved so others won't
have the same problem you did.

Had you experimented as Hubert Depecz Lubaczewksi recommended? If so,
what did you find confusing? If not, why not?

I pose these questions because I know you are not new to PostgreSQL,
nor to posting on these mailing lists, and should be familiar with
with these techniques of exploring PostgreSQL and asking questions.
Or do you consider it easier to have others do this work for you?

And most importantly, have you found the answer to your question?

Michael Glaesemann
grzm seespotcode net