What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
(forwarded from pgsql-interfaces because no response there; can
anybody tell me if I really have a bug, or am just a bit dim?)
Hello,
Below is a test C program, which fetches some timestamp literals and
prints their internal representation, which is the number of seconds
after 2000-01-01, stored as a double. I wrote this program on windows,
so the imports might look unfamiliar to unix users, but it shouldn't
take much effort to port, if you like. My server is 8.1 on Windows XP.
The output from this program is:
-2627158159.000000
-2627156080.000000
-2627156079.000000
-2627156079.000000
which corresponds to timestamps:
1916-10-01 02:25:20 with timezone
1916-10-01 02:25:20 sans timezone
1916-10-01 02:25:21 with timezone
1916-10-01 02:25:21 sans timezone
The first line of output puzzles me: why is '1916-10-01 02:25:20'
2627158159 seconds before 2000-01-01, while '1916-10-01 02:25:21' is
2627156080 before; a difference of 2080 seconds, or 34m:40s.
Is this correct? I don't think so, but there might be some subtlety of
timezone and date arithmetic which I've missed.
Thanks,
Alistair
----------------------------------------
#include <stdio.h>
#include <stdlib.h>
/* for ntohl/htonl
#include <winsock.h>
#include <sys/types.h>
*/
#include "libpq-fe.h"
static void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
void check_sql(PGconn *conn, PGresult *res, ExecStatusType expected)
{
if (PQresultStatus(res) != expected)
{
fprintf(stderr, "SQL failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
}
void check_cmd(PGconn *conn, PGresult *res)
{
check_sql(conn, res, PGRES_COMMAND_OK);
}
void check_qry(PGconn *conn, PGresult *res)
{
check_sql(conn, res, PGRES_TUPLES_OK);
}
void revbytes2(int n, char *pfrom, char *pto)
{
if (n == 0) return;
*pto = *pfrom;
revbytes2(--n, ++pfrom, --pto);
}
void revbytes(int n, void *pfrom, void *pto)
{
revbytes2(n, (char*)pfrom, ((char*)pto)+n-1);
}
void printColOne(PGresult *res)
{
double t, *tptr;
tptr = (double *) PQgetvalue(res, 0, 0);
revbytes(8, tptr, &t);
/* t = ntohl(*tptr); -- this doesn't work!? must be me... */
printf("%f\n", t);
}
int main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
double t, *tptr;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=postgres and using
* environment variables or defaults for all other connection parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "user = postgres";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexecParams(conn, "select timestamp with time zone
'1916-10-01 02:25:20'"
, 0, NULL, NULL, NULL, NULL, 1 );
check_qry(conn, res);
printColOne(res);
PQclear(res);
res = PQexecParams(conn, "select timestamp without time zone
'1916-10-01 02:25:20'"
, 0, NULL, NULL, NULL, NULL, 1 );
check_qry(conn, res);
printColOne(res);
PQclear(res);
res = PQexecParams(conn, "select timestamp with time zone
'1916-10-01 02:25:21'"
, 0, NULL, NULL, NULL, NULL, 1 );
check_qry(conn, res);
printColOne(res);
PQclear(res);
res = PQexecParams(conn, "select timestamp without time zone
'1916-10-01 02:25:21'"
, 0, NULL, NULL, NULL, NULL, 1 );
check_qry(conn, res);
printColOne(res);
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
Import Notes
Reply to msg id not found: 79d7c4980607210754n2778e88cwc3d2e112ceb2ab49@mail.gmail.comReference msg id not found: 79d7c4980607210754n2778e88cwc3d2e112ceb2ab49@mail.gmail.com
"Alistair Bayley" <alistair@abayley.org> writes:
The first line of output puzzles me: why is '1916-10-01 02:25:20'
2627158159 seconds before 2000-01-01, while '1916-10-01 02:25:21' is
2627156080 before; a difference of 2080 seconds, or 34m:40s.
What timezone are you testing in?
Perusing the zic database makes me think it might be Europe/Dublin,
because there's a DST rule with a related breakpoint:
Zone Europe/Dublin -0:25:00 - LMT 1880 Aug 2
-0:25:21 - DMT 1916 May 21 2:00
-0:25:21 1:00 IST 1916 Oct 1 2:00s
0:00 GB-Eire %s 1921 Dec 6 # independence
0:00 GB-Eire GMT/IST 1940 Feb 25 2:00
0:00 1:00 IST 1946 Oct 6 2:00
0:00 - GMT 1947 Mar 16 2:00
0:00 1:00 IST 1947 Nov 2 2:00
0:00 - GMT 1948 Apr 18 2:00
0:00 GB-Eire GMT/IST 1968 Oct 27
1:00 - IST 1971 Oct 31 2:00u
0:00 GB-Eire GMT/IST 1996
0:00 EU GMT/IST
There's a whole raft of comments before that about where the zic people
got their info, so if you have doubts about this, take a look:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/data/europe
regards, tom lane
On 26/07/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Alistair Bayley" <alistair@abayley.org> writes:
The first line of output puzzles me: why is '1916-10-01 02:25:20'
2627158159 seconds before 2000-01-01, while '1916-10-01 02:25:21' is
2627156080 before; a difference of 2080 seconds, or 34m:40s.What timezone are you testing in?
Perusing the zic database makes me think it might be Europe/Dublin,
because there's a DST rule with a related breakpoint:
You are correct. "show TimeZone" and "select * from pg_settings" both
indicate Europe/Dublin.
I was puzzled as to why it is set to Dublin when my machine's Time
Zone is GMT. I saw in the docs that in the absense of an entry in the
.conf file or a TZ environment variable results in a guess; this seems
to be the cause here. I see in
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c?rev=1.44
that the rule seems to prefer shorter names when there's a tie, and
win32_tzmap has Europe/Dublin as the shortest entry in the GMT
section, so perhaps that's the reason... the best choice for me would
have been GMT.
I'll set the server timezone in postgresql.conf to GMT or UTC.
Obviously I've been burnt by timezone conversion... I'll have to read
more about this. Thanks for the link to the timezone data file.
Is it possible for a client to have a different time zone from the
server, or is the only time zone we consider the server time zone? The
latter I think, as the default time zone for a session is the server
time zone (in the absense of a PGTZ variable). Why do we not simply
use TZ on the client, instead of PGTZ?
Also, is it correct for the docs to state that Julian dates are used?
The docs state that the Julian calendar has a year length of 365.2425
days, which is not correct, I think. According to Wikipedia, the
Julian calendar has a year length of 365.25 days, while the Gregorian
calendar has a year length of 365.2425 days. I suspect that the actual
calendar implemented is the Gregorian, and the docs are wrong.
http://en.wikipedia.org/wiki/Julian_calendar
http://en.wikipedia.org/wiki/Gregorian_calendar
Thanks for your help,
Alistair
"Alistair Bayley" <alistair@abayley.org> writes:
I was puzzled as to why it is set to Dublin when my machine's Time
Zone is GMT. I saw in the docs that in the absense of an entry in the
.conf file or a TZ environment variable results in a guess; this seems
to be the cause here. I see in
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c?rev=1.44
that the rule seems to prefer shorter names when there's a tie, and
win32_tzmap has Europe/Dublin as the shortest entry in the GMT
section, so perhaps that's the reason... the best choice for me would
have been GMT.
That is strange, seeing that "GMT" is surely textually shorter. And the
probing function does check as far back as 1916 (indeed back to 1904)
so I don't see why it'd not notice the difference anyway. Could you
trace through it (either with gdb, or add some debug elogs in pgtz.c)
and see why it doesn't give you the right choice?
Is it possible for a client to have a different time zone from the
server, or is the only time zone we consider the server time zone?
Any individual session can SET TIMEZONE to whatever it wants. The point
here is just what the startup default is.
Why do we not simply
use TZ on the client, instead of PGTZ?
Why do you think the client machine is any more likely to have a correct
setting of TZ than the server? I'd guess the opposite myself.
Also, is it correct for the docs to state that Julian dates are used?
I think you're confused about "Julian dates" vs "Julian days". The
latter is just a term for counting from a specific epoch day sometime
back in 4000-something BC. We use the Gregorian calendar though.
regards, tom lane
On 28/07/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Alistair Bayley" <alistair@abayley.org> writes:
I was puzzled as to why it is set to Dublin when my machine's Time
Zone is GMT. I saw in the docs that in the absense of an entry in the
.conf file or a TZ environment variable results in a guess; this seems
to be the cause here. I see in
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c?rev=1.44
that the rule seems to prefer shorter names when there's a tie, and
win32_tzmap has Europe/Dublin as the shortest entry in the GMT
section, so perhaps that's the reason... the best choice for me would
have been GMT.That is strange, seeing that "GMT" is surely textually shorter. And the
probing function does check as far back as 1916 (indeed back to 1904)
so I don't see why it'd not notice the difference anyway. Could you
trace through it (either with gdb, or add some debug elogs in pgtz.c)
and see why it doesn't give you the right choice?
Hmm... I probably should be ashamed to admit it, but I have no
experience of gdb. Could you point me to some kind of guide to running
PostgreSQL in gdb? Is there a wiki-page or something similar? And, is
it reasonably straightforward under windows (I have MinGW/MSYS
installed).
Actually, looking at the code again, I can see what happens, I think.
This entry in win32_tzmap maps my GMT timezone to PG's Europe/Dublin
timezone:
{
"GMT Standard Time", "GMT Daylight Time",
"Europe/Dublin"
}, /* (GMT) Greenwich Mean Time : Dublin,
* Edinburgh, Lisbon, London */
(The first string is the Windows std timezone name, the second is the
daylight-savings timezone name, and the third is the pgsql timezone to
map to.)
So the server deliberately maps GMT to Europe/Dublin. From my POV this
is a dubious decision, but maybe there's a good reason for it.
Is it possible for a client to have a different time zone from the
server, or is the only time zone we consider the server time zone?Any individual session can SET TIMEZONE to whatever it wants. The point
here is just what the startup default is.
The server stores all timestamps at UTC, so then I guess the server
timezone is probably irrelevant, except to use as a default for client
sessions. Or is there another valid use for having a server timezone?
(as opposed to simply running the server at UTC)
Why do we not simply
use TZ on the client, instead of PGTZ?Why do you think the client machine is any more likely to have a correct
setting of TZ than the server? I'd guess the opposite myself.
Well, if the client is in a different timezone from the server, then
using the server timezone as a session default will give you incorrect
conversions from UTC. Or have I got this quite wrong? Is there some
further documentation, or archived email discussions, I could read?
(I'd be surprised if this hasn't been discussed before)
Perhaps a sensible rule for client libs (libpq) might be: use PGTZ if
set, otherwise use TZ if set, otherwise probe the system clock like
the server does; if all alse fails then use the server timezone.
I think you're confused about "Julian dates" vs "Julian days". The
latter is just a term for counting from a specific epoch day sometime
back in 4000-something BC. We use the Gregorian calendar though.
OK. I guess that's something which could be added to the docs; should
I raise that on the -docs list, or just update the online docs myself?
Alistair
"Alistair Bayley" <alistair@abayley.org> writes:
Actually, looking at the code again, I can see what happens, I think.
This entry in win32_tzmap maps my GMT timezone to PG's Europe/Dublin
timezone:
{
"GMT Standard Time", "GMT Daylight Time",
"Europe/Dublin"
}, /* (GMT) Greenwich Mean Time : Dublin,
* Edinburgh, Lisbon, London */
Oh, you didn't say you were on Windows --- that changes things entirely.
That code doesn't try to probe the system behavior, it just has a
hardwired mapping.
Magnus, did you have a specific reason for choosing Europe/Dublin,
or was it just alphabetically first? Europe/London looks at least
marginally closer to what one would think "GMT" means:
Zone Europe/London -0:01:15 - LMT 1847 Dec 1
0:00 GB-Eire %s 1968 Oct 27
1:00 - BST 1971 Oct 31 2:00u
0:00 GB-Eire %s 1996
0:00 EU GMT/BST
Zone Europe/Dublin -0:25:00 - LMT 1880 Aug 2
-0:25:21 - DMT 1916 May 21 2:00
-0:25:21 1:00 IST 1916 Oct 1 2:00s
0:00 GB-Eire %s 1921 Dec 6 # independence
0:00 GB-Eire GMT/IST 1940 Feb 25 2:00
0:00 1:00 IST 1946 Oct 6 2:00
0:00 - GMT 1947 Mar 16 2:00
0:00 1:00 IST 1947 Nov 2 2:00
0:00 - GMT 1948 Apr 18 2:00
0:00 GB-Eire GMT/IST 1968 Oct 27
1:00 - IST 1971 Oct 31 2:00u
0:00 GB-Eire GMT/IST 1996
0:00 EU GMT/IST
regards, tom lane
Oh, you didn't say you were on Windows
I did, but it was buried in the first paragraph...
Magnus, did you have a specific reason for choosing Europe/Dublin,
or was it just alphabetically first? Europe/London looks at least
marginally closer to what one would think "GMT" means:
Does it have to be a specific city? I'd rather it just chose GMT.
Alistair
"Alistair Bayley" <alistair@abayley.org> writes:
Magnus, did you have a specific reason for choosing Europe/Dublin,
or was it just alphabetically first? Europe/London looks at least
marginally closer to what one would think "GMT" means:
Does it have to be a specific city? I'd rather it just chose GMT.
The fact that there is an entry for "GMT Daylight Time" means that
Windows' idea of this time zone is not pure GMT. Or is the translation
table entry a complete work of fiction?
regards, tom lane
Magnus, did you have a specific reason for choosing
Europe/Dublin, or
was it just alphabetically first? Europe/London looks at least
marginally closer to what one would think "GMT" means:Does it have to be a specific city? I'd rather it just chose GMT.
The fact that there is an entry for "GMT Daylight Time" means that
Windows' idea of this time zone is not pure GMT. Or is the
translation table entry a complete work of fiction?
No, it's a work of a simplistic perlscript IIRC. It simply looked for
the first match it could find, based on the list found in the registry
(the whole concept is a bit of an ugly hack, but it's the best we could
come up with). If there is a more fitting timezone for it, it should be
changed.
//Magnus
"Magnus Hagander" <mha@sollentuna.net> writes:
Does it have to be a specific city? I'd rather it just chose GMT.
The fact that there is an entry for "GMT Daylight Time" means that
Windows' idea of this time zone is not pure GMT. Or is the
translation table entry a complete work of fiction?
No, it's a work of a simplistic perlscript IIRC. It simply looked for
the first match it could find, based on the list found in the registry
(the whole concept is a bit of an ugly hack, but it's the best we could
come up with). If there is a more fitting timezone for it, it should be
changed.
I guess the question is whether, when Windows is using this setting,
it tracks British summer time rules or not. Would someone check?
regards, tom lane
On 18/08/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Magnus Hagander" <mha@sollentuna.net> writes:
No, it's a work of a simplistic perlscript IIRC. It simply looked for
the first match it could find, based on the list found in the registry
(the whole concept is a bit of an ugly hack, but it's the best we could
come up with). If there is a more fitting timezone for it, it should be
changed.I guess the question is whether, when Windows is using this setting,
it tracks British summer time rules or not. Would someone check?regards, tom lane
What would a reasonable check be? I can start the Windows command
prompt and type "time /t" which gives me the current local time
(adjusted for daylight savings). In the Windows Date/Time dialogue
there is a "Automatically adjust clock for daylight saving changes"
checkbox, which is checked. I don't know what registry setting this
maps to, though.
Alistair
"Alistair Bayley" <alistair@abayley.org> writes:
On 18/08/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I guess the question is whether, when Windows is using this setting,
it tracks British summer time rules or not. Would someone check?
What would a reasonable check be? I can start the Windows command
prompt and type "time /t" which gives me the current local time
(adjusted for daylight savings). In the Windows Date/Time dialogue
there is a "Automatically adjust clock for daylight saving changes"
checkbox, which is checked. I don't know what registry setting this
maps to, though.
Hm. It kinda sounds like you might get true GMT if that box is not
checked, and the equivalent of Europe/London if it is checked.
I have a vague recollection that we discussed this before and determined
that there's no direct way for a program to find out if that box is
checked though?
regards, tom lane
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 23 August 2006 14:05
To: Alistair Bayley
Cc: Magnus Hagander; pgsql-general@postgresql.org
Subject: Re: [GENERAL] What's special about 1916-10-01
02:25:20? Odd jump in internal timestamptz representationI have a vague recollection that we discussed this before and
determined
that there's no direct way for a program to find out if that box is
checked though?
That particular setting is a DWORD registry key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\
DisableAutoDaylightTimeSet
0 (or non-existant) means the box is checked, 1 when it is cleared.
Regards, Dave.