Frozen Perl 2009
Frozen Perl 2009 is right around the corner, well its on Sat Feb 6. Be there or be cubed (ice cube)...
2 ways to get SQLite to put dates into columns
2 ways to get SQLite to put dates into a column.
insert into mytable values( null, datetime('now') );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|1236711519But 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…
insert into mytable values( null, datetime('now') );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|1236711519But 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…
Comments
Post a Comment