sqlite timestamp formatting

Question

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");
1281353727

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,

Kevin

Answer 1

You need to convert the timestamp to datetime first:

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