SQLite Forum

Query to make timestamp to readable date
Login

Query to make timestamp to readable date

(1) By europa on 2020-03-26 08:57:06 [link]

I have this table:

CREATE TABLE IF NOT EXISTS "smstable" (

	"_id"	INTEGER,
	"thread_id"	INTEGER,
	"address"	TEXT,
	"person"	LONG,
	"date"	LONG,
	"protocol"	INTEGER,
	"read"	INTEGER,
	"status"	INTEGER,
	"type"	INTEGER,
	"reply_path_present"	INTEGER,
	"subject"	TEXT,
	"body"	TEXT,
	"service_center"	TEXT,
	"locked"	INTEGER,
	"error_code"	INTEGER,
	"seen"	INTEGER,
	PRIMARY KEY("_id")
);

INSERT INTO "smstable" VALUES (1,3,'+4222062716',0,1539260029211,0,1,-1,1,0,NULL,'10','+4222001018',0,NULL,NULL);

Now I want to query the table and convert the field "date" into a readable format.

I tried:
SELECT _id, thread_id, address, person, datetime(date, 'unixepoch') FROM smstable

but datetime returns blank.

(2) By Gunter Hick (gunter_hick) on 2020-03-26 09:35:46 in reply to 1

Your input string has 13 digits and is thus out of range for the documented format #12, which is limited to 10 digits.

(3) By Gunter Hick (gunter_hick) on 2020-03-26 09:43:08 in reply to 1 [link]

Please also note that there is no datatype LONG in SQLite. The corresponding colums will have NUMERIC affinity.

"A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT. For the purposes of this paragraph, hexadecimal integer literals are not considered well-formed and are stored as TEXT. (This is done for historical compatibility with versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal integer literals were first introduced into SQLite.) No attempt is made to convert NULL or BLOB values."

(4) By Gunter Hick (gunter_hick) on 2020-03-26 09:49:08 in reply to 1 [link]

Please also note that declaring

"_id" INTEGER, … PRIMARY_KEY("_id")

does NOT invoke the magic INTEGER PRIMARY KEY (access to the internal rowid) feature, which may cause foreign keys to be slower.

Consider using either above magic or adding WITHOUT ROWID to the table declaration, which would make "_id" the real primary key.

(5) By Clemens Ladisch (cladisch) on 2020-03-26 10:48:54 in reply to 1 [link]

You did not specify the format of that date value, but if it is milliseconds since 1970-01-01, then you need to convert it to seconds first:
```
> SELECT datetime(1539260029211 / 1000, 'unixepoch');
2018-10-11 12:13:49
```

(6) By Clemens Ladisch (cladisch) on 2020-03-26 10:55:15 in reply to 4 [link]

The [documentation](https://www.sqlite.org/lang_createtable.html#rowid) actually says that

> if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.

There is no restriction that the primary key constraint must be a column constraint.

(7) By Gunter Hick (gunter_hick) on 2020-03-26 12:28:25 in reply to 6 [link]

Thanks, must have changed when/since WITHOUT ROWID was introduced.