timestamps and dates

"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