Kevin Bradshaw Kevin Bradshaw - 2 months ago 25
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");
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

You need to convert the timestamp to datetime first:

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