How to Convert Unix Epoch Time in SQLite

How can I convert an int (UNIX timestamp) to datetime in SQLite?

The data type of publication_date must be INTEGER and the default value for this column must be 1556399472 which is the equivalent timestamp for 27 April 2019 21:11:12.

There is no problem to store dates as integers in SQLite because it is easy to retrieve a readable date by using the 'unixepoch' modifier with the Date And Time Functions of SQLite.

For example:

SELECT DATETIME(1556399472, 'unixepoch')

returns:

2019-04-27 21:11:12

Also there is no data type VARCHAR in SQLite. You should use TEXT.

So the create statement of your table can be:

CREATE TABLE IF NOT EXISTS posts (
  title TEXT,
  news_description TEXT UNIQUE NOT NULL,
  publication_date INTEGER NOT NULL DEFAULT 1556399472,
  PRIMARY KEY(title)
);

You may remove NOT NULL from the definition of publication_date if you want it to be nullable.

Convert Sqlite Unix Epoch in milliseconds to Datetime YYYY-MM-DD HH:MM:SS.SSS

All date functions accept the same date formats and modifiers, so you can simply remove the nested datetime call.
And you need to do a floating-point division to preserve the milliseconds:

SELECT strftime('%d-%m-%Y %H:%M:%f', time/1000.0, 'unixepoch') FROM table1;

SQLite unix epoch conversion

The result is correct, Feb 7, 2015 and March 7, 2015 are both Saturday. Remember, non-leap-year February has 28 days which divides evenly into 7 day weeks, so March dates will have the same days of the week.

$ cal 2015
                             2015

      January               February               March
Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa
             1  2  3   1  2  3  4  5  6  7   1  2  3  4  5  6  7
 4  5  6  7  8  9 10   8  9 10 11 12 13 14   8  9 10 11 12 13 14
11 12 13 14 15 16 17  15 16 17 18 19 20 21  15 16 17 18 19 20 21
18 19 20 21 22 23 24  22 23 24 25 26 27 28  22 23 24 25 26 27 28
25 26 27 28 29 30 31                        29 30 31

You may be getting confused by time zones. Unix time is the number of seconds since January 1st, 1970 UTC. By default SQLite date functions will use the UTC time zone. If you want your times in the local time zone, you have to tell SQLite that.

sqlite> select strftime('%w', datetime(1425694622000 / 1000, 'unixepoch'));
6
sqlite> select strftime('%w', datetime(1425694622000 / 1000, 'unixepoch'), "localtime");
5

It can be done by either datetime or strftime. I picked strftime because time zones complicate everything so I want to add it as late as possible in the formatting stage.

SQL Query to convert unixepoch time in nanoseconds to human readable time

You were very close with your use of STRFTIME. It will convert the value it gets to a datetime, but since you were using integer math, then it didn't get the fractional time.

SELECT 
    STRFTIME('%Y-%m-%d %H:%M:%f', CAST(timestamp AS float) / 1e9,'unixepoch') AS timestamp

This will do what you want. First convert the timestamp to a float value, then divide by 10⁹ to convert the value to a unix epoch.

SQLite doesn't seem to convert unix epoch correctly

You are currently using milliseconds but you need to use seconds:

select datetime(timestamp / 1000, 'unixepoch', 'localtime')
from history

From the documentation:

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.

---

Related Topics