CODEWITHSUNDEEP
sqlitedatetimetimestamp
pratnala
pratnala

Reputation: 3843

Get records for the latest timestamp in SQLite

I know this question has been asked before by others but all those questions use either MySQL or other databases. How to do in SQLite? I am asking since SQLite doesn't have a separate date data type. I have created the table with this schema

Fragment:

CREATE TABLE x (
id INTEGER NOT NULL,
timestamp TIMESTAMP NOT NULL,
PRIMARY KEY(timestamp,id)
);

Will something like this work?

SELECT * FROM x
WHERE DATE(timestamp) = (
    SELECT MAX(DATE(timestamp)) FROM x
)

Upvotes: 7

Views: 15839

Answers (1)

laalto
laalto

Reputation: 152847

Timestamps like 2014-03-05T20:00:00.000 have the property that the lexicographic (alphabetical) ordering is the same as chronological ordering and you can use plain old comparison operators and ORDER BY on them, like this:

SELECT * FROM x ORDER BY timestamp DESC LIMIT 1;

Upvotes: 20

Related Questions