SQLite strftime() returning null with integers

Question

I have a date with the column name "date" in my table "productions" stored as integer with Unix format ( example: 1548263300000). And I want to retrieve only the year. When I do:

SELECT strftime('%Y ',date) as year
FROM productions

it returns null.

When I change the type to TEXT into my table and I store the date in string format ( example 2020-05-01 ), the same sql returns me "2020" which is correct and what I was looking for.

Why strftime() doesn't work with integers since the SQLite documentation say you can work with TEXT,INTEGER and REAL for dates? How to use date functions with integers?

extra information:

In this tutorial, they also use strftime with integers and it seems to work for them, so I understand from that, that the functions are available no matter what type you use ( text,int,real): link

when I use:

SELECT strftime('%Y',DATETIME(ROUND(date/ 1000), 'unixepoch')) 
FROM productions;

it works fine, but I don't understand why I have to do all this when I use integers but when I use text, it works directly.

forpas forpas · Accepted Answer · 2020-05-27T19:43:24

You can use strftime() but you have to add the 'unixepoch' modifier:

strftime('%Y', date / 1000, 'unixepoch')

so your date / 1000 is recognized as the number of seconds since 1970-01-01.

From Date And Time Functions:

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970.

SQLite strftime() returning null with integers

1 Answers