Menu
Is it possible to get the difference (in seconds) between two TIMESTAMP values in Sqlite3?
For instance, I've tried the following query:
SELECT CURRENT_TIMESTAMP - my_timestamp FROM my_table;
And I always get '0'. Can anyone tell me what I'm doing wrong? (Note, I have verified that my_timestamp is indeed in the past.)
677
To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR . To get the difference in seconds as we have done here, choose SECOND .
The schema is SYSIBM. An expression that returns a value of a built-in character string or a graphic string data type that is not a LOB. The value is expected to be the result of subtracting two timestamps and converting the result to a character string of length 22.
To get the difference between two-time, subtract time1 from time2. A result is a timedelta object. The timedelta represents a duration which is the difference between two-time to the microsecond resolution. To get a time difference in seconds, use the timedelta.
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.
Got it:
SELECT (julianday(CURRENT_TIMESTAMP) - julianday(my_timestamp)) * 86400.0) FROM my_table;
julianday returns the fractional number of days since noon in Greenwich on November 24, 4714 B.C. I then take the difference and multiply by the number of seconds per day.
113
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