2 ways to get SQLite to put dates into columns

2 ways to get SQLite to put dates into a column.

1. insert into mytable values( null, datetime('now') );
2. insert into mytable values( null, strftime('%s', 'now'));

The first one inserts a row somewhat like this:
1|2009-03-10 18:47:46

The second inserts an unix timestamp:
2|1236711411

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');

Output of:

id|lu
6|1236643200
2|1236711411
3|1236711516
4|1236711518
5|1236711519

But 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;
id|lu
6|2009-03-10 00:00:00
2|2009-03-10 18:56:51
3|2009-03-10 18:58:36
4|2009-03-10 18:58:38
5|2009-03-10 18:58:39

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.