Skip to content

Instantly share code, notes, and snippets.

@bockor
Created July 2, 2025 11:51
Show Gist options
  • Save bockor/8ade83eef84cbc57da84390525838d2f to your computer and use it in GitHub Desktop.
Save bockor/8ade83eef84cbc57da84390525838d2f to your computer and use it in GitHub Desktop.
SQLITE DATE - TIME - DATETIME notes
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