Warning: Undefined array key "action" in /home/bakers/html/entryreply.php on line 5

Warning: Undefined variable $output in /home/bakers/html/entryreply.php on line 50

Warning: Undefined array key "EntrySubject" in /home/bakers/html/entryreply.php on line 51

Warning: Undefined variable $logintext in /home/bakers/html/entryreply.php on line 97

Warning: Undefined variable $reply_name in /home/bakers/html/entryreply.php on line 103

Warning: Undefined variable $reply_email in /home/bakers/html/entryreply.php on line 104
Perturb.org - interesting technology related things from around the internet

SQLite and Dates

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

This will go the other way. Take a unixtime, and convert it to a SQLite date format.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Some semi-official SQLite documentation on how it handles dates is availble on their wiki page.



Note: Replies will be formatted with PHP Markdown Extra syntax.

Name: Email (Not Required):
 
Logged IP: 3.236.239.91
To prevent spam please submit by clicking the kitten: