Created
July 2, 2025 11:51
-
-
Save bockor/8ade83eef84cbc57da84390525838d2f to your computer and use it in GitHub Desktop.
SQLITE DATE - TIME - DATETIME notes
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SQLITE DATE - TIME - DATETIME YOUTUBE TUT | |
https://www.youtube.com/watch?v=nJRvz5Rhrx0 | |
# Insert DEFAULT timestamp "now" as INTEGER | |
# and unixepoch prior SQLITE 3.38 (2022-02-22). | |
#################################################### | |
create table t1 (c1 TEXT, c2 INTEGER DEFAULT (strftime('%s', 'now'))); | |
insert into t1(c1) values ('drink'),('eat'); | |
select * from t1; | |
drink|1685170712 | |
eat|1685170712 | |
select c1, typeof(c2) from t1; | |
drink|integer | |
eat|integer | |
select c1, datetime(c2, 'unixepoch') from t1; | |
drink|2023-05-27 06:58:32 | |
eat|2023-05-27 06:58:32 | |
starting SQLITE 3.38 (2022-02-22). | |
################################# | |
unixepoch function | |
select unixepoch('2022-03-09'); | |
select unixepoch('now'); | |
1646784000 | |
insert into t(c) values (unixepoch('now')); | |
select c, date(c, 'unixepoch') from t; | |
1730454933|2024-11-01 | |
1730455004|2024-11-01 | |
select c, ' -> ',datetime(c, 'unixepoch') from t; | |
1730487104| -> |2024-11-01 18:51:44 | |
1730544845| -> |2024-11-02 10:54:05 | |
Date & Time Functions | |
- date() "YYYY-MM-DD" | |
- julianday() Real | |
- unixepoch() Integer | |
- time() "HH:MM:SS" | |
- datetime() "YYYY-MM-DD HH:MM:SS" | |
how to calculate the difference between two timestamps | |
################################# | |
https://learnsql.com/cookbook/how-to-calculate-the-difference-between-two-timestamps-in-sqlite/ | |
id departure arrival | |
1 2018-03-25 12:00:00 2018-04-05 07:30:00 | |
2 2019-09-12 15:50:00 2019-10-23 10:30:30 | |
SELECT | |
id, | |
departure, | |
arrival, | |
JULIANDAY(arrival) - JULIANDAY(departure) AS difference | |
FROM travel; | |
id departure arrival difference | |
1 2018-03-25 12:00:00 2018-04-05 07:30:00 10.8125 | |
2 2019-09-12 15:50:00 2019-10-23 10:30:30 40.778125000186265 | |
SELECT | |
id, | |
departure, | |
arrival, | |
ROUND((JULIANDAY(arrival) - JULIANDAY(departure)) * 86400) AS difference | |
FROM travel; | |
id departure arrival difference | |
1 2018-03-25 12:00:00 2018-04-05 07:30:00 934200 | |
2 2019-09-12 15:50:00 2019-10-23 10:30:30 3523230 | |
********** | |
select | |
round( | |
julianday( | |
date( | |
unixepoch('now'), | |
'unixepoch' | |
) | |
) - julianday('2024-03-10') | |
) as delay | |
┌───────┐ | |
│ delay │ | |
├───────┤ | |
│ 243.0 │ | |
└───────┘ | |
********************************************************** | |
CREATE TABLE deliveries ( | |
id INTEGER PRIMARY KEY, | |
company TEXT, | |
delivery INTEGER); | |
insert into deliveries(company,delivery) values ('nero pew', unixepoch('2022-08-29')); | |
┌────┬──────────┬────────────┐ | |
│ id │ company │ delivery │ | |
├────┼──────────┼────────────┤ | |
│ 1 │ brutus │ 1677974400 │ | |
│ 2 │ palmo │ 1685923200 │ | |
│ 3 │ calcutta │ 1735516800 │ | |
│ 4 │ bear │ 1729814400 │ | |
│ 5 │ nero pew │ 1661731200 │ | |
└────┴──────────┴────────────┘ | |
select | |
company, | |
date(delivery, 'unixepoch') as delivery | |
from | |
deliveries | |
where | |
julianday( | |
date('now') | |
) - julianday( | |
date(delivery, 'unixepoch') | |
) > 730; | |
┌──────────┬────────────┐ | |
│ company │ delivery │ | |
├──────────┼────────────┤ | |
│ nero pew │ 2022-08-29 │ | |
└──────────┴────────────┘ | |
select | |
company, | |
CAST( | |
julianday( | |
date('now') | |
) - julianday( | |
date(delivery, 'unixepoch') | |
) as int | |
) as daysago, | |
date(delivery, 'unixepoch') | |
from | |
deliveries; | |
┌──────────┬─────────┬─────────────────────────────┐ | |
│ company │ daysago │ date(delivery, 'unixepoch') │ | |
├──────────┼─────────┼─────────────────────────────┤ | |
│ brutus │ 614 │ 2023-03-05 │ | |
│ palmo │ 522 │ 2023-06-05 │ | |
│ calcutta │ -52 │ 2024-12-30 │ | |
│ bear │ 14 │ 2024-10-25 │ | |
│ nero pew │ 802 │ 2022-08-29 │ | |
└──────────┴─────────┴─────────────────────────────┘ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment