Interesting. I suppose these variable results are because of each system's
localtime() function?
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select datetime('2038-12-31 00:00:00');
2038-12-31 00:00:00
sqlite> select strftime('%s', '2038-12-31');
-2147483648
sqlite> select date(-2147483648, 'unixepoch');
1901-12-13
sqlite>
Platform is Linux (Ubuntu Feisty x86 32-bit);
Same results for Win32;
On Solaris, it appears to work differently, getting a positive result from
strftime(), and the correct year, but the month, day, and time are off:
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> select datetime('2038-12-31 00:00:00');
2038-12-31 00:00:00
sqlite> select strftime('%s', '2038-12-31 00:00:00');
2147483647
sqlite> select datetime(2147483647, 'unixepoch');
2038-01-19 03:14:07
sqlite>
-Clark
----- Original Message ----
From: Igor Tandetnik <[email protected]>
To: [email protected]
Sent: Wednesday, April 1, 2009 11:42:30 AM
Subject: Re: [sqlite] 2038 year problem
denisgolovan <[email protected]>
wrote:
> I'd like to ask a question about 2038 year unix problem.
> I've found a mention about it on on
> http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions wiki
> page.
> Though I cannot grasp the idea of the following phase:
>
> "Also, the localtime() C function normally only works for years
> between 1970 and 2037. For dates outside this range, SQLite attempts
> to map the year into an equivalent year within this range, do the
> calculation, then map the year back. "
>
> Does it mean 2038 must be a problem or not?
When you do something like
select datetime('2038-12-31T00:00:00', 'localtime');
SQLite has to convert the time you gave from UTC to local time. It uses
localtime() C function for this. On many systems, this function only
accepts dates between 1970 and 2037. To work around this, SQLite
actually passes a different date to the function: it has the same month,
day and time as the one you specify, but different year (adjusted so
that it falls into an accepted range). After localtime() call, it
adjusts the year back by the same amount.
> Now I'm trying to deal with 2038-2039 year dates, but function date
> just returns nulls for all such date strings. Maybe I miss something?
This works for me
select date('2038-12-31T00:00:00');
and returns the expected value of '2038-12-31'. Show the exact code you
have a problem with.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
