'NOW' in UTC with no timezone

[Stuart Bishop]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi.

I'm trying to determine the best way of saying 'The current time in UTC
with no time zone information'.

I'm currently using CURRENT_TIMESTAMP AT TIME ZONE 'UTC' and inserting
into columns defined as TIMESTAMP WITHOUT TIME ZONE which appears to
work. However, PostgreSQL parses this into the much more confusing
"timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone)"
which is what is appearing on my generated documentation.

Is there any magic string like 'NOW'::timestamp or CURRENT_TIMESTAMP
which returns UTC time?

- --
Stuart Bishop <st****@stuartbishop.net>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBa2XUAfqZj7rGN0oRAkLJAJ9vOWl1hDSbubKQUnCSvB Zg8nzvwACdFjvV
9vACiPZyhnXjlLZuTbGoUrs=
=gEL/
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Stuart Bishop <st****@stuartbishop.net> writes:

I'm trying to determine the best way of saying 'The current time in UTC
with no time zone information'.

Isn't that a contradiction in terms?

I *think* maybe what you want is to SET TIMEZONE = 'UTC' and then
stop worrying about it. But anyone who is worried about timezones
and yet is storing his data in timestamp-without-time-zone columns
probably needs to reconsider exactly what his data represents.
What is it that you actually want to store, and how do you want
it presented?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Stark]

Tom Lane <tg*@sss.pgh.pa.us> writes:

Stuart Bishop <st****@stuartbishop.net> writes:

I'm trying to determine the best way of saying 'The current time in UTC
with no time zone information'.
Isn't that a contradiction in terms?

Not if you're used to the Unix concept of storing "seconds since the epoch".
In that model the quantity you're storing is entirely time zone agnostic.
But anyone who is worried about timezones and yet is storing his data in
timestamp-without-time-zone columns probably needs to reconsider exactly
what his data represents.

The SQL approach of storing a time zone with the timestamp makes things very
confusing. For unix people it requires a time zone in precisely the opposite
circumstances from when they expect to use one. And It means two timestamps
representing the same point in time can have subtly different behaviours if
they're stored with different time zones.

I think what this user wants is to store a "timestamp with time zone" and
always store his time with the time zone "UTC". That lets him store timestamps
using the time since epoch mentality, but print them accurately in whatever
time zone he wants.

If you stored them "without time zone" then postgres wouldn't let you easily
display them in non-UTC time zones. It considers them to be a particular time
of a particular day in whatever time zone you're in.

It could be useful to represent "3pm in your local time zone" which can be
useful for some purposes. For example, I'm using it to represent the expiry
time of specials, since they expire on a particular date in your local time
zone. If you transport the printout from one time zone to another the expiry
time actually changes. In practice I would have been just as happy storing UTC
and then printing using "AT TIMEZONE UTC".

--
greg
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Karsten Hilbert]

> > > I'm trying to determine the best way of saying 'The current time in UTC

with no time zone information'.

Isn't that a contradiction in terms?

Not if you're used to the Unix concept of storing "seconds since the epoch".
In that model the quantity you're storing is entirely time zone agnostic.

But then one is storing an interval, not a point in time.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Glaesemann]

On Oct 12, 2004, at 9:43 PM, Karsten Hilbert wrote:

I'm trying to determine the best way of saying 'The current time in
UTC
with no time zone information'.

Isn't that a contradiction in terms?

Not if you're used to the Unix concept of storing "seconds since the
epoch".
In that model the quantity you're storing is entirely time zone
agnostic.

But then one is storing an interval, not a point in time.

By that logic, all "times" are intervals. '2004-10-12 22:09' is 2004
years, 10 months, 12 days, 22 hours, 9 minutes since 0.

Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Greg Stark <gs*****@mit.edu> writes:

Tom Lane <tg*@sss.pgh.pa.us> writes:

Stuart Bishop <st****@stuartbishop.net> writes:

I'm trying to determine the best way of saying 'The current time in UTC
with no time zone information'.
Isn't that a contradiction in terms?

Not if you're used to the Unix concept of storing "seconds since the epoch".
In that model the quantity you're storing is entirely time zone agnostic.
Not at all. In my worldview, the Unix concept is "seconds since
midnight 1/1/1970 00:00 UTC", and therefore it is essentially UTC time,
because (a) its absolute meaning doesn't change depending on your local
timezone, but (b) unless you are in UTC, you have to rotate it to your
local timezone for display.

For comparison, various not-Unix operating systems get this wrong, and
store seconds since local-time midnight, simplifying display at the
price of not knowing what time it Really Is.
The SQL approach of storing a time zone with the timestamp makes things very
confusing. For unix people it requires a time zone in precisely the opposite
circumstances from when they expect to use one.
Yes, obviously you are confused ;-)

Postgres implements TIMESTAMP WITH TIME ZONE as the Unix concept: what
is stored internally is seconds since the UTC epoch. We rotate to or
from local timezone for input/display. TIMESTAMP WITHOUT TIME ZONE is
essentially the other idea: it stores seconds since a local-midnight
epoch in an unspecified time zone. No timezone adjustment is done
during input or display.

If timezones are at all significant in terms of your application, you
almost certainly want to be storing your data as TIMESTAMP WITH TIME ZONE,
which amounts to asserting that you know what time the values Really Are
in global terms. Otherwise the rotation facilities are going to be
fighting you every step of the way.

(Note that this is arguably not what the SQL standard means by TIMESTAMP
WITH TIME ZONE, but it's what Postgres implements.)
It could be useful to represent "3pm in your local time zone" which can be
useful for some purposes.

TIME WITHOUT TIME ZONE?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Greg Stark]

Tom Lane <tg*@sss.pgh.pa.us> writes:

Greg Stark <gs*****@mit.edu> writes:

Tom Lane <tg*@sss.pgh.pa.us> writes:

Stuart Bishop <st****@stuartbishop.net> writes:
I'm trying to determine the best way of saying 'The current time in UTC
with no time zone information'.

Isn't that a contradiction in terms?

Not if you're used to the Unix concept of storing "seconds since the epoch".
In that model the quantity you're storing is entirely time zone agnostic.

Not at all. In my worldview, the Unix concept is "seconds since
midnight 1/1/1970 00:00 UTC", and therefore it is essentially UTC time,
because (a) its absolute meaning doesn't change depending on your local
timezone, but (b) unless you are in UTC, you have to rotate it to your
local timezone for display.

Well one sense it has no time zone since it's just a quantity of time. The
number of seconds since the epoch to a particular point in time is the same no
matter where you are. In another sense it's related to UTC because the epoch
is specified in UTC. That's why the user's description of "The current time in
UTC with no time zone information" is applicable.
The SQL approach of storing a time zone with the timestamp makes things very
confusing. For unix people it requires a time zone in precisely the opposite
circumstances from when they expect to use one.

Yes, obviously you are confused ;-)

Hm. Further experimentation shows I was indeed confused.

I guess my confusion comes from the way postgres interprets unadorned time
stamps as being in local time. And then always displays timestamps converted
to local time. I thought it was remembering the time zone specified in the
original input. In fact it's not doing that.

I am beginning to like the idea you suggested of leaving the server set to UTC
and just manually specifying time zones whenever I want to convert to local
time.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Greg Stark <gs*****@mit.edu> writes:

I guess my confusion comes from the way postgres interprets unadorned time
stamps as being in local time. And then always displays timestamps converted
to local time. I thought it was remembering the time zone specified in the
original input. In fact it's not doing that.

Indeed not. (I think that the SQL spec contemplates that TIMESTAMP WITH
TIME ZONE *should* work that way, but that's not what we've done.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

On Tue, Oct 12, 2004 at 10:43:09AM -0400, Tom Lane wrote:

Greg Stark <gs*****@mit.edu> writes:

I guess my confusion comes from the way postgres interprets unadorned time
stamps as being in local time. And then always displays timestamps converted
to local time. I thought it was remembering the time zone specified in the
original input. In fact it's not doing that.
Indeed not. (I think that the SQL spec contemplates that TIMESTAMP WITH
TIME ZONE *should* work that way, but that's not what we've done.)

In something I'm working on at the moment I've settled on storing the
timestamp and the timezone in seperate columns. The reason is that it
really needs to represent time in a particular timezone. The operation
of adding one day to a timestamp is dependant on a particular timezone
due to daylight savings. If everything is always rotated to your
current timezone the results will just be wrong...

Since PostgreSQL doesn't actually support daylight savings timezones
I'm going to do the processing in the application. I'd consider adding
it to PostgreSQL too except this needs to work on pre-8.0 systems.

Maybe what is needed is a TIMESTAMP WITH FIXED TIME ZONE type :)

Have a nice day,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBbAO3Y5Twig3Ge+YRAoPgAJ0ZMfujjDAJIHPZnJa4Nl aQnrVgdACfZBQB
sjY1W8QzJi4Tfani5X1BpN4=
=djBS
-----END PGP SIGNATURE-----

[Stuart Bishop]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
| Stuart Bishop <st****@stuartbishop.net> writes:
|
|>I'm trying to determine the best way of saying 'The current time in UTC
|>with no time zone information'.
|
|
| Isn't that a contradiction in terms?

Not at all - I want 'now' in UTC time without the extra backage of the
timezone information, since it is a well known fact. The functionality
of python's datetime.utcnow() or time.gmtime() basically. I can get
this, but it looks a bit ugly and confusing. I'll probably solve this
using a stored procedure.

| I *think* maybe what you want is to SET TIMEZONE = 'UTC' and then
| stop worrying about it. But anyone who is worried about timezones
| and yet is storing his data in timestamp-without-time-zone columns
| probably needs to reconsider exactly what his data represents.
| What is it that you actually want to store, and how do you want
| it presented?

I've got that set on the production server - this is mainly to ensure
that developers on their local instances are catered for and to make
things explicit. It makes the other developers more aware of what is
going on rather than things just accidently working in most cases. We
are a Python house and not a Perl house in other words :-)

How much overhead is there in storing a timestamp with timezone as
opposed to one without? You would need an extra few bits for the offset
but I don't know if that affects the total number of bytes to store it.

- --
Stuart Bishop <st****@stuartbishop.net>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBblAhAfqZj7rGN0oRAhhcAJ9c9o8Q6gK900U4hwqEjg/3bTyHIgCfY9x6
pMp+Iw3Yxrck0jIZCUz8ryk=
=mQ+L
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Stuart Bishop <st****@stuartbishop.net> writes:

How much overhead is there in storing a timestamp with timezone as
opposed to one without?

Exactly zero. You have a misconception about what the datatype really
does --- see other responses in this thread.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Stuart Bishop]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
| Stuart Bishop <st****@stuartbishop.net> writes:
|
|>How much overhead is there in storing a timestamp with timezone as
|>opposed to one without?
|
|
| Exactly zero. You have a misconception about what the datatype really
| does --- see other responses in this thread.

Indeed - I was under the impression that the timezone would be preserved
(which is the case in the external datetime libraries I use), but I now
see that PostgreSQL will lose this information.

- --
Stuart Bishop <st****@stuartbishop.net>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBb05DAfqZj7rGN0oRAnz3AJwLqPBMY1MNxjeXjg/orFWNI4+MrwCfSTyG
wSZ0Hmo6Bg9y6ZgfItJOf3w=
=xSUI
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jaromir Dolecek]

Stuart Bishop wrote:

Indeed - I was under the impression that the timezone would be preserved
(which is the case in the external datetime libraries I use), but I now
see that PostgreSQL will lose this information.

Err - how come, lose?

Jaromir
--
Jaromir Dolecek <jd******@NetBSD.org> http://www.NetBSD.cz/
-=- We should be mindful of the potential goal, but as the Buddhist -=-
-=- masters say, ``You may notice during meditation that you -=-
-=- sometimes levitate or glow. Do not let this distract you.'' -=-

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

On Fri, Oct 15, 2004 at 06:48:40AM +0200, Jaromir Dolecek wrote:

Stuart Bishop wrote:

Indeed - I was under the impression that the timezone would be preserved
(which is the case in the external datetime libraries I use), but I now
see that PostgreSQL will lose this information.
Err - how come, lose?

It doesn't remember what timezone to gave when you entered the data. It
converts it to a date/time and displays it in your local timezone.

In other words, postgresql, treats the following as identical:

# select '2004-09-01 12:0:0 CEST'::timestamptz;
timestamptz
------------------------
2004-09-01 12:00:00+02
(1 row)

# select '2004-09-01 20:0:0 AEST'::timestamptz;
timestamptz
------------------------
2004-09-01 12:00:00+02
(1 row)

The answer is correct, but you're getting less out than you put in..

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBb5vFY5Twig3Ge+YRAkzUAKCizElL7axhWxsMUg0oFO Qu0omhBQCfUNFx
3xa3MpKXnY5+pgWqbzcfIOo=
=Ya98
-----END PGP SIGNATURE-----