SQLITE DATE - TIME - DATETIME notes

gistfile1.txt

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                  │
└──────────┴─────────┴─────────────────────────────┘