Thread: 'NOW' in UTC with no timezone
-----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 <stuart@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
iD8DBQFBa2XUAfqZj7rGN0oRAkLJAJ9vOWl1hDSbubKQUnCSvBZg8nzvwACdFjvV
9vACiPZyhnXjlLZuTbGoUrs=
=gEL/
-----END PGP SIGNATURE-----
Stuart Bishop <stuart@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
Tom Lane <tgl@sss.pgh.pa.us> writes: > Stuart Bishop <stuart@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
> > > 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
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
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Stuart Bishop <stuart@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
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > >> Stuart Bishop <stuart@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
Greg Stark <gsstark@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
On Tue, Oct 12, 2004 at 10:43:09AM -0400, Tom Lane wrote: > Greg Stark <gsstark@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 <kleptog@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.
Attachment
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: | Stuart Bishop <stuart@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 <stuart@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-----
Stuart Bishop <stuart@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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: | Stuart Bishop <stuart@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 <stuart@stuartbishop.net> http://www.stuartbishop.net/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBb05DAfqZj7rGN0oRAnz3AJwLqPBMY1MNxjeXjg/orFWNI4+MrwCfSTyG wSZ0Hmo6Bg9y6ZgfItJOf3w= =xSUI -----END PGP SIGNATURE-----
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 <jdolecek@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.'' -=-
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 <kleptog@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.