SELECT date(DateField) FROM Table; SELECT time(DateField) FROM Table; SELECT datetime(DateField) FROM Table;If you want unix time (seconds since the epoch) you have to format the output.
SELECT strftime("%s",DateField) FROM Table;However that will return the time in UTC which is probably not what you want (it's not what I wanted). I want it to compensate for my local timezone and thus you have to tell it to use your timezone.
SELECT strftime("%s",DateField,'localtime') FROM Table;
Related Posts:
Green Data: Pysqlite
Sources: Perturb.org , Sqlite Wiki
Tags: Sqlite, Database, Gr33n Data
Thanks for posting this... I googled for "sqlite date" and your blog post came up... Gave me pretty much what I needed to know, so cool.
ReplyDeleteThanks again.
You're welcome Armchair :)
ReplyDeleteSame thing for me, I googled for "sqlite data types date". Thanks
ReplyDeleteSeems you'remarque on date is correct :
ReplyDeletesqlite> select strftime("%s", "2004-06-01 12:00:00") ;
1086091200
When using a different format it gives nothing back :
sqlite> select strftime("%s", "01-06-2004 12:00:00") ;
Using sqlite : SQLite version 3.6.10
Thanks