Mario Frasca wrote:
I tried the datetime functions, but:
sqlite> select datetime('now');
2006-08-03 11:36:32
sqlite> select typeof(datetime('now'));
text
sqlite> select datetime('now') - date('2006-01-01');
0
sqlite> select typeof(datetime('now') - date('2006-01-01'));
integer
where I would expect:
sqlite> select datetime('now');
2006-08-03 11:36:32.211032
sqlite> select typeof(datetime('now'));
datetime
sqlite> select datetime('now') - date('2006-01-01');
214 11:36:51.291331
sqlite> select typeof(datetime('now') - date('2006-01-01'));
timedelta
Mario,
The DATE and DATETIME types you seem to be expecting are handled in
SQLite as Julian dates. These are floating point numbers that hold the
number of days since noon in Greenwich on November 24, 4714 B.C. Hours
and seconds are represented as fractional days.
The TIMEDELTA type is also represented as a floating point number, the
difference between any two dates as Julian day numbers. This holds the
number of days and fractional days between any two dates. If you want
hours between the dates, multiply by 24. If you want seconds between the
dates multiply by 86400. If you just want the days, cast to an integer
to removed the fractional part.
Try these queries instead:
sqlite> select julianday('now');
2453951.29464256
sqlite> select typeof(julianday('now'));
real
sqlite> select julianday('now') - julianday('2006-01-01');
214.794642561581
sqlite> select typeof(julianday('now') - julianday('2006-01-01'));
real
If you really want the mixed format result for your second query use
something like this:
sqlite> select cast(julianday('now') - julianday('2006-01-01') as
integer) ||
...> ' ' || time(julianday('now') - julianday('2006-01-01'));
214 07:14:05
HTH
Dennis Cote
