Thread: timestamps and dates
I'm sure this has cropped up before but I can't find the messages so sorry to
bother everyone...
I have two systems (both linux) one, let's call it A, running 7.3.1 and one, B,
7.3.2, although I think that difference is irrelevent as I show below.
Trying:
SELECT '2003 Jul 08'::timestamptz
on A gives me the expected:
'2003-07-08 00:00:00+01'
while on B I get:
'2003-07-07 23:59:00+01'
(time zone being set for the UK)
I even reran the regression tests and on B I get:
circle ... ok
date ... ok
time ... ok
timetz ... ok
timestamp ... ok
timestamptz ... ok
interval ... ok
abstime ... ok
reltime ... ok
tinterval ... ok
inet ... ok
And when I look at the output I do indeed correct results like:
SELECT '' AS to_timestamp_3, to_timestamp('1985 January 12', 'YYYY FMMonth DD');
to_timestamp_3 | to_timestamp
----------------+------------------------------
| Sat Jan 12 00:00:00 1985 PST
(1 row)
So obviously I'm missing something (and it appears not to be a version
thing). Any help?
TIA
--
Nigel J. Andrews
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> SELECT '2003 Jul 08'::timestamptz
> while on B I get:
> '2003-07-07 23:59:00+01'
> (time zone being set for the UK)
Is machine B perhaps using leap-second-aware timekeeping? Postgres
doesn't cope with that ...
regards, tom lane
On Mon, 28 Apr 2003, Nigel J. Andrews wrote: > > > I'm sure this has cropped up before but I can't find the messages so sorry to > bother everyone... > > I have two systems (both linux) one, let's call it A, running 7.3.1 and one, B, > 7.3.2, although I think that difference is irrelevent as I show below. > > Trying: > > SELECT '2003 Jul 08'::timestamptz > > on A gives me the expected: > > '2003-07-08 00:00:00+01' > > while on B I get: > > '2003-07-07 23:59:00+01' > > (time zone being set for the UK) To follow up my own posting: On B I have now explicitly set the timezone to BST and the above works as expected. In the environment I have been starting my connections from, including the web server, doesn't contain TZ but then neither does the 'A' installation. This makes it seem like the problem is OS installation on 'B'. Indeed, I didn't have a /etc/timezone file on there. Although, I have just added it and it makes no difference. This is must be a problem with my Linux knowledge since I've no idea how `date` was aware of the timezone without either TZ or /etc/timezone. Both systems are not setting timezone in postgres.conf however, system A is a debian 2.2 while system B is immunix (based on Redhat 7.0). In answer to Tom's question in reply about B using leap second accounting, I don't know. Someone here probably can say without thinking whether RH 7.0 did or not. Anyway, I am now happy I can generate a work around easily even if I don't understand why it's necessary. I would of course prefer to understand why. Thanks for the quick reply as usual Tom, -- Nigel Andrews
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> In answer to Tom's question in reply about B using leap second accounting, I
> don't know. Someone here probably can say without thinking whether RH 7.0 did
> or not.
I believe this is a property of the timezone file you are using. But
like you, I dunno what determines the default timezone when neither TZ
nor /etc/timezone is set. Anyone?
regards, tom lane
On Mon, 28 Apr 2003, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > In answer to Tom's question in reply about B using leap second accounting, I
> > don't know. Someone here probably can say without thinking whether RH 7.0 did
> > or not.
>
> I believe this is a property of the timezone file you are using. But
> like you, I dunno what determines the default timezone when neither TZ
> nor /etc/timezone is set. Anyone?
info libc says this (in the node 'Running make install'):
To configure the locally used timezone, set the `TZ' environment
variable. The script `tzselect' helps you to select the right
value. As an example, for Germany, `tzselect' would tell you to
use `TZ='Europe/Berlin''. For a system wide installation (the
given paths are for an installation with `--prefix=/usr'), link
the timezone file which is in `/usr/share/zoneinfo' to the file
`/etc/localtime'. For Germany, you might execute `ln -s
/usr/share/zoneinfo/Europe/Berlin /etc/localtime'.
My system (Gentoo 1.4 w/ glibc 2.3.1) didn't have /etc/timezone at all. So
I did a little googling and found that it's mostly used by programs
tzconfig/tzsetup etc.
And what comes to leap second accounting, the leap seconds were introduced
in 1972 and after that only ~35 leap seconds have been added to UTC.
You could try this on your box (it *might* work):
% date +%s -d '31-dec-1998 23:59:60'
915141600
% date +%s -d '1-jan-1999 00:00:00'
915141600
If there's one second difference in numbers it implies that leap second
accounting is on in your timezone file.
BTW, I found a rather interesting page "Astronomical Time Keeping", which
contains lots of information about timezones, calendars, different UTs,
leap seconds, leap years...: http://www.maa.mhn.de/Scholar/times.html
--
Antti Haapala
On Tue, 29 Apr 2003, Antti Haapala wrote: > > On Mon, 28 Apr 2003, Tom Lane wrote: > > > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > > In answer to Tom's question in reply about B using leap second accounting, I > > > don't know. Someone here probably can say without thinking whether RH 7.0 did > > > or not. > > > > I believe this is a property of the timezone file you are using. But > > like you, I dunno what determines the default timezone when neither TZ > > nor /etc/timezone is set. Anyone? > > info libc says this (in the node 'Running make install'): > > To configure the locally used timezone, set the `TZ' environment > variable. The script `tzselect' helps you to select the right > value. As an example, for Germany, `tzselect' would tell you to > use `TZ='Europe/Berlin''. For a system wide installation (the > given paths are for an installation with `--prefix=/usr'), link > the timezone file which is in `/usr/share/zoneinfo' to the file > `/etc/localtime'. For Germany, you might execute `ln -s > /usr/share/zoneinfo/Europe/Berlin /etc/localtime'. > > My system (Gentoo 1.4 w/ glibc 2.3.1) didn't have /etc/timezone at all. So > I did a little googling and found that it's mostly used by programs > tzconfig/tzsetup etc. Thanks, reading the above quote though it was the localtime file I was in search of. I just couldn't remember it's name right. Looking at the offending system the /etc/localtime is a hardlink to the timezone description. So it still doesn't explain why the unknown->timestamptz cast wasn't behaving correctly...although...localtime is linked to /usr/share/zoneinfo/right/GB where as may be setting TZ or explicitly setting the GUC is picking up /usr/share/zoneinfo/posix/GB or /usr/share/zoneinfo/GB. I think I might try that out tomorrow during a break. > And what comes to leap second accounting, the leap seconds were introduced > in 1972 and after that only ~35 leap seconds have been added to UTC. > > You could try this on your box (it *might* work): > > % date +%s -d '31-dec-1998 23:59:60' > 915141600 > % date +%s -d '1-jan-1999 00:00:00' > 915141600 > > If there's one second difference in numbers it implies that leap second > accounting is on in your timezone file. I'm definitely going to try this out tomorrow during a break. > > BTW, I found a rather interesting page "Astronomical Time Keeping", which > contains lots of information about timezones, calendars, different UTs, > leap seconds, leap years...: http://www.maa.mhn.de/Scholar/times.html -- Nigel J. Andrews
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> localtime is linked to /usr/share/zoneinfo/right/GB
> where as may be setting TZ or explicitly setting the GUC is picking up
> /usr/share/zoneinfo/posix/GB or /usr/share/zoneinfo/GB.
IIRC, the "posix" ones are non-leap-second-aware. Systems which have a
"right" directory have leap-second-aware timezone files in that
subdirectory.
As for how "right" it is to track leap seconds, I note that the powers
that control such things are seriously considering abandoning leap
seconds. Having unpredictable future differences between UTC and TAI
sucks.
regards, tom lane
On Mon, 28 Apr 2003, Nigel J. Andrews wrote: > On Tue, 29 Apr 2003, Antti Haapala wrote: > > > And what comes to leap second accounting, the leap seconds were introduced > > in 1972 and after that only ~35 leap seconds have been added to UTC. > > > > You could try this on your box (it *might* work): > > > > % date +%s -d '31-dec-1998 23:59:60' > > 915141600 > > % date +%s -d '1-jan-1999 00:00:00' > > 915141600 > > > > If there's one second difference in numbers it implies that leap second > > accounting is on in your timezone file. > > I'm definitely going to try this out tomorrow during a break. This works better: The number of seconds from Epoch in POSIX compliant timezones at even hours is divisible by 3600... :) % export TZ=Europe/Helsinki % date +%s -d 00:00:00 1051563600 ...while... % export TZ=right/Europe/Helsinki % date +%s -d 00:00:00 1051563622 So zones in 'right' folder have leap second support on. The difference is correct - 22 (i had it wrong before), the number of leap seconds inserted since UTC Epoch on 1 Jan 1972. -- Antti Haapala +358 50 369 3535 ICQ: #177673735
On Tue, 29 Apr 2003, Antti Haapala wrote:
> On Mon, 28 Apr 2003, Nigel J. Andrews wrote:
>
> > On Tue, 29 Apr 2003, Antti Haapala wrote:
> >
> > > And what comes to leap second accounting, the leap seconds were introduced
> > > in 1972 and after that only ~35 leap seconds have been added to UTC.
> > >
> > > You could try this on your box (it *might* work):
> > >
> > > % date +%s -d '31-dec-1998 23:59:60'
> > > 915141600
> > > % date +%s -d '1-jan-1999 00:00:00'
> > > 915141600
> > >
> > > If there's one second difference in numbers it implies that leap second
> > > accounting is on in your timezone file.
> >
> > I'm definitely going to try this out tomorrow during a break.
>
> This works better: The number of seconds from Epoch in POSIX compliant
> timezones at even hours is divisible by 3600... :)
>
> % export TZ=Europe/Helsinki
> % date +%s -d 00:00:00
> 1051563600
>
> ...while...
>
> % export TZ=right/Europe/Helsinki
> % date +%s -d 00:00:00
> 1051563622
>
> So zones in 'right' folder have leap second support on. The difference is
> correct - 22 (i had it wrong before), the number of leap seconds inserted
> since UTC Epoch on 1 Jan 1972.
Hmm...
db=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
ilmo=# select '1998-31-12 23:59:59 UTC'::timestamp with time zone;
timestamptz
------------------------
1999-01-01 01:59:59+02
(1 row)
ilmo=# select '1998-31-12 23:59:60 UTC'::timestamp with time zone;
ERROR: Bad timestamp external representation '1998-31-12 23:59:60
UTC'
My timestamp surely is legal according to ISO-8601.
--
Antti Haapala
Antti Haapala <antti.haapala@iki.fi> writes:
>> So zones in 'right' folder have leap second support on. The difference is
>> correct - 22 (i had it wrong before), the number of leap seconds inserted
>> since UTC Epoch on 1 Jan 1972.
Yeah. That's the second report we've had of systems running in a
leap-second zone by default. I think it would be a good idea for
Postgres to check for this situation and complain. But how strongly
should it complain? Refuse to start up? Adopt GMT instead? What if
asking for GMT gets a leap-second zone?
> ilmo=# select '1998-31-12 23:59:60 UTC'::timestamp with time zone;
> ERROR: Bad timestamp external representation '1998-31-12 23:59:60 UTC'
> My timestamp surely is legal according to ISO-8601.
That's a good point. We got complaints about this all the time back
when we had roundoff problems in that code, but no one ever stopped to
point out that such a timestamp actually is legal per spec. (Strictly
speaking I think :60 should only be accepted at points where there
actually was a leap second, but we're not gonna check for that...)
regards, tom lane