CODEWITHSUNDEEP
sqlsqlitedatetime
EJ_
EJ_

Reputation: 65

What is the resolution of SQLite date time functions?

Looking at the functions here. The formatting shows fractional seconds available to the millisecond (0.001) but is it accurate to the millisecond? I have not been able to find the resolution of these calls in any of the documentation.

Upvotes: 1

Views: 1607

Answers (3)

Kevin-Prichard
Kevin-Prichard

Reputation: 163

If you want to store time values with higher resolution than is permitted by the date and time functions, you can opt for INTEGER storage, but you're on your own for conversion functions and the like.

Storing unix epoch timestamps with up to nanosecond precision will cover dates from 1970 until minimally the year 2262 (if nanoseconds). You're on your own for conversion to/from ISO date strings and time unit-relative arithmetic, but if you're content with that, then you'll have a fast, high precision and compact storage format.

I have a system that receives sensor values from devices at a rate timed in nanoseconds, so storing and indexing on those timestamps has been very helpful. I do have to provide a query interface that converts from ISO, Python datetime and other formats into ns and back, but that's the deal.

Working with 8-byte timestamps helped keep the record length short, with quick inserts and queries.

Upvotes: 0

CL.
CL.

Reputation: 180020

This depends on the date format.

INTEGER numbers are accurate to the second.

TEXT values are accurate to the millisecond. You could specify more digits in the fractional seconds fields, but the built-in function will ignore all after the first three.

The resolution of Julian day numbers is better than a millisecond, but when formatting them, the built-in functions will not output more than three fractional digits.

Upvotes: 1

Caius
Caius

Reputation: 243

https://www.sqlite.org/datatype3.html#section_2_2

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").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

It appears that you can reach the highest resolution with ISO8601 strings. There should be no problem with accuracy with these strings, as long as you're not mixing storage representations.

Upvotes: 2

Related Questions