Menu
Date and Time Datatype. 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.
Using INTEGER to store SQLite date and time values First, create a table that has one column whose data type is INTEGER to store the date and time values. Second, insert the current date and time value into the datetime_int table. Third, query data from the datetime_int table. It's an integer.
The unixepoch() function returns a unix timestamp - the number of seconds since 1970-01-01 00:00:00 UTC. The unixepoch() always returns an integer, even if the input time-value has millisecond precision. The strftime() routine returns the date formatted according to the format string specified as the first argument.
When defining a relation, I want to update an attribute to the timestamp at insert. For example, a working table that I have right now
CREATE TABLE t1( id INTEGER PRIMARY KEY AUTOINCREMENT, time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, txt TEXT); This is updating a timestamp on insert, for example, insert into t1 (txt) values ('hello') adds the row 1|2012-07-19 08:07:20|hello|. However, I want to have this date formatted in unixepoch format.
I read the docs but this wasn't clear. For example, I modified the table relation to time TIMESTAMP DEFAULT DATETIME('now','unixepoch') but I get an error. Here, as in the docs, now was my time string and unixepoch was the modifier but it didn't work. Could someone help me how to format it as a unixepoch timestamp?
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