Menu
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
)
543
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
The SQLite function DATE('now') returns the current date as a text value. It uses the 'YYYY-MM-DD' format, where YYYY is a 4-digit year, MM is a 2-digit month, and DD is a 2-digit day of the month. This function takes one argument: the text 'now' indicates the current date and time.
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS. SSS").
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
