sqlite timestamp formatting

You need to convert the timestamp to datetime first:

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

This worked for me:

datetime(visit_date/1000000,'unixepoch')

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'))