Are you looking for my non-technical blog?

This is now my technical-only blog, my non-technical blog is here.

12 January 2006

Sqlite - Date and Time Data Types

SQLite handles dates quite strangely. If you create a table and put a date in it, the only way SQLite will take the input is "2004-06-01 12:00:00" anything else it won't recognize as a date and thus none of the date functions will work. Assuming you have a date entered propely (like above) you get date information from the db like so.
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: , ,

4 comments:

  1. 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.

    Thanks again.

    ReplyDelete
  2. You're welcome Armchair :)

    ReplyDelete
  3. Same thing for me, I googled for "sqlite data types date". Thanks

    ReplyDelete
  4. Seems you'remarque on date is correct :

    sqlite> 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

    ReplyDelete