Хранение дат и времени в базе данных

SQLite не имеет встроенных типов данных для хранения в таблицах в базе данных дат и времени, тем не менее это не мешает хранить дату и время в базе данных также, как и любые другие данные. Для хранения дат и времени можно использовать типы TEXT, REAL и INTEGER

Тип TEXT

При использовании типа TEXT для хранения даты и времени они сохраняются в формате YYYY-MM-DD HH:MM:SS.SSS или YYYY-MM-DD HH:MM:SS, например, 2021-12-01 13:23:08

Для хранения отдельно даты применяется формат YYYY-MM-DD, а для хранения времени - формат HH:MM:SS

Например, определим следующую таблицу пользователей:

DROP TABLE IF EXISTS users;
CREATE TABLE users
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
    date_of_birth TEXT
);
INSERT INTO users (name, date_of_birth) VALUES 
('Tom', '1987-05-12'),
('Bob', date('now', '-41 years')),
('Sam', date('2021-11-29', '-25 years'));

Для установки даты и времени при сохранении данных можно напрямую использовать текстовый формат ("1987-05-12") или применять функции для работы с датами и временем (date('now', '-41 years')).

Хранимые значения мы можем далее использовать в функциях, которые работают с датами и временем. Например, используя хранимую дату дня рождения, найдем возраст пользователей:

SELECT name, 
	date('now')-date_of_birth AS age 
FROM users;

Другой пример - получение пользователей, день рождения которых находится в определенном диапазоне дат:

SELECT name, date_of_birth 
FROM users
WHERE date_of_birth BETWEEN '1950-01-01' AND '1991-12-31';

Аналогичным образом можно сохранять дату и время полностью. Например, определим таблицу заказов, которая хранит дату и время заказа:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date_time TEXT NOT NULL
);
INSERT INTO orders (date_time)
VALUES ('2021-11-30 22:01:15'),
(datetime('now')),
(datetime('now', '-5 hours'));

SELECT * FROM orders;

К примеру найдем все заказы за ноябрь 2021 года:

SELECT * FROM orders
WHERE date_time BETWEEN '2021-11-01' AND '2021-11-30';

Или найдем заказы, сделанные за текущий день:

SELECT * FROM orders
WHERE date_time BETWEEN datetime('now', 'start of day') AND datetime('now');

REAL

Также можно хранить дату и время в виде значения REAL. В этом случае столбец хранит количество дней с начала юлианской эпохи. Например, возьмем предущую таблицу заказов и изменим в ней для даты и времени заказа тип с TEXT на REAL:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date_time REAL NOT NULL
);
INSERT INTO orders (date_time)
VALUES (julianday('2021-11-30 22:01:15')),
(julianday('now')),
(julianday('now', '-5 hours'));

Хранимое значение REAL с помощью функцию мы можем преобразовать в дату и время:

SELECT date_time, datetime(date_time) FROM orders;

INTEGER

Также можно использовать тип INTEGER. В этом случае дата и время хранятся в виде количества секунд, прошедших с начала эпохи UNIX (хотя это ограничивает набор дат началом эпохи UNIX - 1970-01-01 00:00:00 UTC). Например, возьмем предыдущую таблицу заказов и изменим в ней для даты и времени заказа тип на INTEGER:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date_time INTEGER NOT NULL
);
INSERT INTO orders (date_time)
VALUES (strftime('%s', '2021-11-30 22:01:15')),
(strftime('%s', 'now')),
(strftime('%s', 'now', '-5 hours'));

Функция strftime с помощью параметра "%s"получает количество секунд, прошедших с начала эпохи UNIX относительно заданной даты. И также это значение мы можем преобразовать в читабельные дату и время:

SELECT date_time, datetime(date_time, 'unixepoch') FROM orders;