Re: timestamps and dates - Mailing list pgsql-general

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