High-precision date/time in SQLite
While SQLite provides a certain number of date functions, I wanted something more. So I've created
sqlean-time — a high-precision date/time extension with a structured API and a rich set of functions.
Note. Adding extensions to SQLite is a breeze. Download a file, run one database command — and you are good to go.
Concepts • Creating values • Extracting fields • Unix time • Time comparison • Time arithmetic • Rounding • Formatting • Acknowledgements • Installation and usage
Concepts
This extension works with two types of values: Time and Duration.
Time
since within
0-time second
┌─────────┬─────────────┐
│ seconds │ nanoseconds │
└─────────┴─────────────┘
64 bit 32 bit
Time is a pair (seconds, nanoseconds), where seconds is the 64-bit number of seconds since zero time (0001-01-01 00:00:00 UTC) and nanoseconds is the number of nanoseconds within the current second (0-999999999).
For maximum flexibility, you can store time values in their internal representation (a 13-byte BLOB). This allows you to represent dates for billions of years in the past and future with nanosecond precision.
Alternatively, you can store time values as a NUMBER (64-bit integer) of seconds (milli-, micro- or nanoseconds) since the Unix epoch (1970-01-01 00:00:00 UTC). In this case, the range of representable dates depends on the unit of time used:
- Seconds: billions of years into the past or future with second precision.
- Milliseconds: 292 million years before or after 1970 with millisecond precision.
- Microseconds: years from -290307 to 294246 with microsecond precision.
- Nanoseconds: years from 1678 to 2262 with nanosecond precision.
Time is always stored and operated in UTC, but you can convert it from/to a specific timezone offset.
Duration
┌─────────────┐
│ nanoseconds │
└─────────────┘
64 bit
Duration is a 64-bit number of nanoseconds, so it can represent values up to about 290 years. You can store duration values as NUMBER.
The calendrical calculations always assume a Gregorian calendar, with no leap seconds.
Creating time values
There are two basic constructors. For the current time:
select time_fmt_iso(time_now());
-- 2024-08-06T21:22:15.431295000Z
2024-08-06T21:22:15.431295000Z
And for a specific date/time:
select time_fmt_iso(time_date(2011, 11, 18));
-- 2011-11-18T00:00:00Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));
-- 2011-11-18T15:56:35Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888));
-- 2011-11-18T15:56:35.666777888Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 0, 3*3600));
-- 2011-11-18T12:56:35Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888, 3*3600));
-- 2011-11-18T12:56:35.666777888Z
2011-11-18T00:00:00Z
2011-11-18T15:56:35Z
2011-11-18T15:56:35.666777888Z
2011-11-18T12:56:35Z
2011-11-18T12:56:35.666777888Z
Extracting time fields
There are a number of functions for extracting different date/time fields:
select 'year = ' || time_get_year(time_now());
select 'month = ' || time_get_month(time_now());
select 'day = ' || time_get_day(time_now());
select 'hour = ' || time_get_hour(time_now());
select 'minute = ' || time_get_minute(time_now());
select 'second = ' || time_get_second(time_now());
select 'nanosec = ' || time_get_nano(time_now());
select 'weekday = ' || time_get_weekday(time_now());
select 'yearday = ' || time_get_yearday(time_now());
select 'isoyear = ' || time_get_isoyear(time_now());
select 'isoweek = ' || time_get_isoweek(time_now());
year = 2024
month = 8
day = 8
hour = 11
minute = 2
second = 29
nanosec = 646015917
weekday = 4
yearday = 221
isoyear = 2024
isoweek = 32
There is also a generic time_get function for extracting time fields by name:
select 'millennium = ' || time_get(time_now(), 'millennium');
select 'century = ' || time_get(time_now(), 'century');
select 'decade = ' || time_get(time_now(), 'decade');
select 'year = ' || time_get(time_now(), 'year');
select 'quarter = ' || time_get(time_now(), 'quarter');
select 'month = ' || time_get(time_now(), 'month');
select 'day = ' || time_get(time_now(), 'day');
select 'hour = ' || time_get(time_now(), 'hour');
select 'minute = ' || time_get(time_now(), 'minute');
select 'second = ' || time_get(time_now(), 'second');
select 'milli = ' || time_get(time_now(), 'milli');
select 'micro = ' || time_get(time_now(), 'micro');
select 'nano = ' || time_get(time_now(), 'nano');
select 'isoyear = ' || time_get(time_now(), 'isoyear');
select 'isweek = ' || time_get(time_now(), 'isoweek');
select 'isodow = ' || time_get(time_now(), 'isodow');
select 'yearday = ' || time_get(time_now(), 'yearday');
select 'weekday = ' || time_get(time_now(), 'weekday');
select 'epoch = ' || time_get(time_now(), 'epoch');
millennium = 2
century = 20
decade = 202
year = 2024
quarter = 3
month = 8
day = 8
hour = 11
minute = 7
second = 8.800600319
milli = 800
micro = 800733
nano = 800750131
isoyear = 2024
isweek = 32
isodow = 4
yearday = 221
weekday = 4
epoch = 1723115228.80122
Unix time
There are functions for creating time values from Unix time (time since the Unix epoch — January 1, 1970 UTC):
select time_fmt_iso(time_unix(1321631795));
-- 2011-11-18T15:56:35Z
select time_fmt_iso(time_unix(1321631795, 666777888));
-- 2011-11-18T15:56:35.666777888Z
select time_fmt_iso(time_milli(1321631795666));
-- 2011-11-18T15:56:35.666000000Z
select time_fmt_iso(time_micro(1321631795666777));
-- 2011-11-18T15:56:35.666777000Z
select time_fmt_iso(time_nano(1321631795666777888));
-- 2011-11-18T15:56:35.666777888Z
2011-11-18T15:56:35Z
2011-11-18T15:56:35.666777888Z
2011-11-18T15:56:35.666000000Z
2011-11-18T15:56:35.666777000Z
2011-11-18T15:56:35.666777888Z
And functions for converting time values back to Unix time:
select time_to_unix(time_now());
-- 1722979335
select time_to_milli(time_now());
-- 1722979335431
select time_to_micro(time_now());
-- 1722979335431295
select time_to_nano(time_now());
-- 1722979335431295000
1723119379
1723119379000
1723119379000192
1723119379000199404
Unix-like operating systems often record time as a 32-bit number of seconds, but since time_to_unix returns a 64-bit value, it is valid for billions of years into the past or future. time_to_milli can represent dates 292 million years before or after 1970, time_to_micro — years from -290307 to 294246, and time_to_nano — years from 1678 to 2262.
Time comparison
time_after, time_before and time_compare compare time values:
select time_after(time_now(), time_date(2011, 11, 18));
-- 1
select time_before(time_now(), time_date(2011, 11, 18));
-- 0
select time_compare(time_now(), time_date(2011, 11, 18));
-- 1
select time_compare(time_date(2011, 11, 18), time_now());
-- -1
select time_compare(time_date(2011, 11, 18), time_date(2011, 11, 18));
-- 0
1
0
1
-1
0
time_equal reports whether two time values represent the same time instant:
select time_equal(time_now(), time_date(2011, 11, 18));
-- 0
select time_equal(time_date(2011, 11, 18), time_date(2011, 11, 18));
-- 1
0
1
Time arithmetic
time_add adds duration to a time value (use negative duration to subtract):
select time_fmt_iso(time_add(time_now(), 24*dur_h()));
-- 2024-08-07T21:22:15.431295000Z
select time_fmt_iso(time_add(time_now(), 60*dur_m()));
-- 2024-08-06T22:22:15.431295000Z
select time_fmt_iso(time_add(time_now(), 5*dur_m()+30*dur_s()));
-- 2024-08-06T21:27:45.431295000Z
2024-08-09T12:28:16.686654780Z
2024-08-08T13:28:16.686695178Z
2024-08-08T12:33:46.686707964Z
You can use the following duration constants:
dur_us()- 1 microsecond;dur_ms()- 1 millisecond;dur_s()- 1 second;dur_m()- 1 minute;dur_h()- 1 hour.
Do not use time_add to add days, months or years. Use time_add_date instead.
time_add_date adds a given number of years, months, and days to a time value (use negative values to subtract):
select time_fmt_date(time_add_date(time_date(2011, 11, 18), 5));
-- 2016-11-18
select time_fmt_date(time_add_date(time_date(2011, 11, 18), 3, 5));
-- 2015-04-18
select time_fmt_date(time_add_date(time_date(2011, 11, 18), 3, 5, -10));
-- 2015-04-08
2016-11-18
2015-04-18
2015-04-08
time_sub returns the duration between two time values (in nanoseconds):
select time_sub(time_date(2011, 11, 19), time_date(2011, 11, 18));
-- 86400000000000
select time_sub(
time_date(2011, 11, 18, 16, 56, 35),
time_date(2011, 11, 18, 15, 56, 35)
);
-- 3600000000000
select time_sub(time_unix(1321631795, 5000000), time_unix(1321631795, 0));
-- 5000000
86400000000000
3600000000000
5000000
time_since returns the time elapsed since a given time (in nanoseconds):
select time_since(time_now());
-- 5000
5000
time_until returns the duration until a given time (in nanoseconds):
select time_until(time_date(2024, 9, 1));
-- 2144479530297000
2144479530297000
Rounding
time_trunc truncates a time value to the precision specified by a given field:
with t as (
select time_date(2011, 11, 18, 15, 56, 35, 666777888) as v
)
select 'original = ' || time_fmt_iso(t.v) from t union all
select 'millennium = ' || time_fmt_iso(time_trunc(t.v, 'millennium')) from t union all
select 'century = ' || time_fmt_iso(time_trunc(t.v, 'century')) from t union all
select 'decade = ' || time_fmt_iso(time_trunc(t.v, 'decade')) from t union all
select 'year = ' || time_fmt_iso(time_trunc(t.v, 'year')) from t union all
select 'quarter = ' || time_fmt_iso(time_trunc(t.v, 'quarter')) from t union all
select 'month = ' || time_fmt_iso(time_trunc(t.v, 'month')) from t union all
select 'week = ' || time_fmt_iso(time_trunc(t.v, 'week')) from t union all
select 'day = ' || time_fmt_iso(time_trunc(t.v, 'day')) from t union all
select 'hour = ' || time_fmt_iso(time_trunc(t.v, 'hour')) from t union all
select 'minute = ' || time_fmt_iso(time_trunc(t.v, 'minute')) from t union all
select 'second = ' || time_fmt_iso(time_trunc(t.v, 'second')) from t union all
select 'milli = ' || time_fmt_iso(time_trunc(t.v, 'milli')) from t union all
select 'micro = ' || time_fmt_iso(time_trunc(t.v, 'micro')) from t;
original = 2011-11-18T15:56:35.666777888Z
millennium = 2000-01-01T00:00:00Z
century = 2000-01-01T00:00:00Z
decade = 2010-01-01T00:00:00Z
year = 2011-01-01T00:00:00Z
quarter = 2011-10-01T00:00:00Z
month = 2011-11-01T00:00:00Z
week = 2011-11-12T00:00:00Z
day = 2011-11-18T00:00:00Z
hour = 2011-11-18T15:00:00Z
minute = 2011-11-18T15:56:00Z
second = 2011-11-18T15:56:35Z
milli = 2011-11-18T15:56:35.666000000Z
micro = 2011-11-18T15:56:35.666777000Z
Alternatively, time_trunc rounds a time value down to a multiple of a given duration:
with t as (
select time_date(2011, 11, 18, 15, 56, 35, 666777888) as v
)
select 't = ' || time_fmt_iso(t.v) from t union all
select '12h = ' || time_fmt_iso(time_trunc(t.v, 12*dur_h())) from t union all
select '1h = ' || time_fmt_iso(time_trunc(t.v, dur_h())) from t union all
select '30m = ' || time_fmt_iso(time_trunc(t.v, 30*dur_m())) from t union all
select '1m = ' || time_fmt_iso(time_trunc(t.v, dur_m())) from t union all
select '30s = ' || time_fmt_iso(time_trunc(t.v, 30*dur_s())) from t union all
select '1s = ' || time_fmt_iso(time_trunc(t.v, dur_s())) from t;
t = 2011-11-18T15:56:35.666777888Z
12h = 2011-11-18T12:00:00Z
1h = 2011-11-18T15:00:00Z
30m = 2011-11-18T15:30:00Z
1m = 2011-11-18T15:56:00Z
30s = 2011-11-18T15:56:30Z
1s = 2011-11-18T15:56:35Z
time_round rounds a time value to the nearest multiple of a given duration:
with t as (
select time_date(2011, 11, 18, 15, 56, 35, 666777888) as v
)
select 't = ' || time_fmt_iso(t.v) from t union all
select '12h = ' || time_fmt_iso(time_round(t.v, 12*dur_h())) from t union all
select '1h = ' || time_fmt_iso(time_round(t.v, dur_h())) from t union all
select '30m = ' || time_fmt_iso(time_round(t.v, 30*dur_m())) from t union all
select '1m = ' || time_fmt_iso(time_round(t.v, dur_m())) from t union all
select '30s = ' || time_fmt_iso(time_round(t.v, 30*dur_s())) from t union all
select '1s = ' || time_fmt_iso(time_round(t.v, dur_s())) from t;
t = 2011-11-18T15:56:35.666777888Z
12h = 2011-11-18T12:00:00Z
1h = 2011-11-18T16:00:00Z
30m = 2011-11-18T16:00:00Z
1m = 2011-11-18T15:57:00Z
30s = 2011-11-18T15:56:30Z
1s = 2011-11-18T15:56:36Z
Formatting
time_fmt_iso returns an ISO 8601 time string for a given time value (optionally converts the time value to a given timezone offset before formatting):
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888), 3*3600);
-- 2011-11-18T18:56:35.666777888+03:00
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888));
-- 2011-11-18T15:56:35.666777888Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35), 3*3600);
-- 2011-11-18T18:56:35+03:00
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));
-- 2011-11-18T15:56:35Z
2011-11-18T18:56:35.666777888+03:00
2011-11-18T15:56:35.666777888Z
2011-11-18T18:56:35+03:00
2011-11-18T15:56:35Z
time_fmt_datetime, time_fmt_date and time_fmt_time return a datetime/date/time string for a given time value:
select time_fmt_datetime(time_date(2011, 11, 18, 15, 56, 35), 3*3600);
-- 2011-11-18 18:56:35
select time_fmt_datetime(time_date(2011, 11, 18, 15, 56, 35));
-- 2011-11-18 15:56:35
select time_fmt_datetime(time_date(2011, 11, 18));
-- 2011-11-18 00:00:00
select time_fmt_date(time_date(2011, 11, 18, 15, 56, 35), 12*3600);
-- 2011-11-19
select time_fmt_date(time_date(2011, 11, 18, 15, 56, 35));
-- 2011-11-18
select time_fmt_date(time_date(2011, 11, 18));
-- 2011-11-18
select time_fmt_time(time_date(2011, 11, 18, 15, 56, 35), 3*3600);
-- 18:56:35
select time_fmt_time(time_date(2011, 11, 18, 15, 56, 35));
-- 15:56:35
select time_fmt_time(time_date(2011, 11, 18));
-- 00:00:00
2011-11-18 18:56:35
2011-11-18 15:56:35
2011-11-18 00:00:00
2011-11-19
2011-11-18
2011-11-18
18:56:35
15:56:35
00:00:00
time_parse parses a formatted string and returns the time value it represents:
select time_parse('2011-11-18T15:56:35.666777888Z')
= time_unix(1321631795, 666777888);
select time_parse('2011-11-18T19:26:35.666777888+03:30')
= time_unix(1321631795, 666777888);
select time_parse('2011-11-18T12:26:35.666777888-03:30')
= time_unix(1321631795, 666777888);
select time_parse('2011-11-18T15:56:35Z')
= time_unix(1321631795, 0);
select time_parse('2011-11-18T19:26:35+03:30')
= time_unix(1321631795, 0);
select time_parse('2011-11-18T12:26:35-03:30')
= time_unix(1321631795, 0);
select time_parse('2011-11-18 15:56:35')
= time_unix(1321631795, 0);
select time_parse('2011-11-18')
= time_date(2011, 11, 18);
select time_parse('15:56:35')
= time_date(1, 1, 1, 15, 56, 35);
1
1
1
1
1
1
1
1
1
Supports a limited set of layouts:
2006-01-02T15:04:05.999999999+07:00 ISO 8601 with nanoseconds and timezone
2006-01-02T15:04:05.999999999Z ISO 8601 with nanoseconds, UTC
2006-01-02T15:04:05+07:00 ISO 8601 with timezone
2006-01-02T15:04:05Z ISO 8601, UTC
2006-01-02 15:04:05 Date and time, UTC
2006-01-02 Date only, UTC
15:04:05 Time only, UTC
Duration constants
There are functions that return common durations in nanoseconds:
select dur_ns();
-- 1
select dur_us();
-- 1000
select dur_ms();
-- 1000000
select dur_s();
-- 1000000000
select dur_m();
-- 60000000000
select dur_h();
-- 3600000000000
1
1000
1000000
1000000000
60000000000
3600000000000
Acknowledgements
While this extension is implemented in C, its design and implementation is largely based on Go's stdlib time package (BSD 3-Clause License), which I think is awesome (except for the formatting part).
Installation and usage
Download the latest release
Use with SQLite command-line interface:
sqlite> .load ./time
sqlite> select time_now();
See also:
──
P.S. Interactive examples in this post are powered by codapi — an open source tool I'm building. Use it to embed live code snippets into your product docs, online course or blog.
★ Subscribe to keep up with new posts.