view raw
Kevin Bradshaw Kevin Bradshaw - 8 months ago 78
SQL Question

sqlite timestamp formatting

I am trying to work with dates in an sqlite database. I am storing my dates as timestamps, but when I use strftime() to format them to human readable dates I am getting back unxpected results.

Consider the following, I select the current timestamp:

SELECT strftime("%s","now");

Then I try to format a date using the timestamp that I know to represent now expecting to get back a human readable format of todays date:

SELECT strftime('%d - %m - %Y ', 1281353727);
01 - 04 - 3503

Instead I get the above result. Is this correct behaviour? am I doing something wrong?

Thanks in advance,



You need to convert the timestamp to datetime first:

SELECT strftime('%d - %m  - %Y ', datetime(1281353727, 'unixepoch')) FROM Visits;