- insert into mytable values( null, datetime('now') );
- insert into mytable values( null, strftime('%s', 'now'));
The second inserts an unix timestamp:
It might be best to use that unix timestamp with an integer column type for dates since SQLite doesn't support a datetime one.
It makes comparisons and ordering much easier:
select * from dt where lu > strftime('%s', '2009-03-10');
id|luBut the formatting is pretty ugly. :-/
Hey what about formatting it within the select with the SQLite datetime function:
select id, datetime(lu, 'unixepoch') lu from dt order by lu;Better but having to add that to each select is kind of a pain. I like adding a new column better:
select *, datetime(lu, 'unixepoch') date from dt order by lu;
Alright done playing now... It would be probably be good to add a language extension for a new function called 'now()'. Some other day :)
For more datetime SQLite information, see the SQLite date reference.