Обсуждение: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
От
"Alistair Bayley"
Дата:
(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;
}
"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
Re: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
От
"Alistair Bayley"
Дата:
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
Re: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
От
"Alistair Bayley"
Дата:
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
Re: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
От
"Alistair Bayley"
Дата:
> 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
Re: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
От
"Magnus Hagander"
Дата:
> >> 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
Re: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
От
"Alistair Bayley"
Дата:
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 representation > > 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? 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.