sqlite timestamp formatting

user_409826 0
Viewed 54811

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

date-formatting sqlite sql database
3 Answers

Convert the timestamp to datetime and then format it:

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

catch 1

Make sure you add the localtime argument. Otherwise you will get a date as perceived by the people living in "Greenwich" (since timestamp started counting at 1/1/1970 00:00 UTC (=GMT) ).

See below how the date output changes depending on whether we use 'localtime', on a workstation with EEST timezone.

select strftime('%d - %m  - %Y ', datetime(1623707777, 'unixepoch', 'localtime'))

output: 15 - 06 - 2021

select strftime('%d - %m  - %Y ', datetime(1623707777, 'unixepoch'))

output: 14 - 06 - 2021

This is Because at 00:01 EEST, the GMT is still at yesterday.

catch 2

Many programs (e.g. ViberPC) add milliseconds (or even nanoseconds) also to timestamp, in which case you need to first divide by 1000 (or by 1000000 respectively):

select strftime('%d - %m  - %Y ', datetime(1623707777421/1000, 'unixepoch', 'localtime'))
user_15600020
Related