SQLite 備忘録: time

ラベル time の投稿を表示しています。すべての投稿を表示

2022年1月29日土曜日

SQLite3 日付・時刻の処理のまとめ

ver 3.35.3 時点

外部参考サイト

SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html

2022年1月8日土曜日

SQLite3で時間の計算をする

日時の処理のまとめ

概要

SQLiteで時間の計算をするにはtime()、もしくはdatetime()を使用する。
なお、time()の結果は時間部分だけではなく日付部分を持っており、必ず2000年1月1日になる。

構文


time(value, modifier, modifier, ...)
datetime(value, modifier, modifier, ...)

実行例

環境

Windows 10 64bit
SQLite3 ver.3.37.0

時（hours）の計算

time()や datetime()の引数にNNN hoursを追加する。
例えば１時間後を計算したければ+1 hoursを追加する。

datetime()であれば日を跨ぐ計算もしてくれる。
time()の場合は日を跨ぐ計算をしても、結果は2000年1月1日の扱いになる。


    sqlite> select time('09:00:00', '+1 hours');
    10:00:00

    sqlite> select time('09:00:00', '-1 hours');
    08:00:00

    sqlite> select time('23:00:00', '+2 hours');
    01:00:00

    sqlite> select time('01:00:00', '-2 hours');
    23:00:00

    sqlite> select datetime('2021-01-01 09:00:00', '+1 hours');
    2021-01-01 10:00:00

    sqlite> select datetime('2021-01-01 09:00:00', '-1 hours');
    2021-01-01 08:00:00

    sqlite> select datetime('2021-01-01 23:00:00', '+2 hours');
    2021-01-02 01:00:00

    sqlite> select datetime('2021-01-01 01:00:00', '-2 hours');
    2020-12-31 23:00:00

time()の結果は何をしても2000年1月1日になる

SQLite3 time()のサンプル


    sqlite> select datetime(time('23:00:00', '+2 hours'));
    2000-01-01 01:00:00

    sqlite> select datetime(time('2021-09-01 23:00:00', '+2 hours'));
    2000-01-01 01:00:00

    sqlite> select datetime(time('2021-09-01 01:00:00', '-2 hours'));
    2000-01-01 23:00:00

分（minutes）の計算

time()やdatetime()の引数にNNN minutesを追加する。
例えば１分後を計算したければ+1 minutesを追加する。

時（hours）を跨ぐ計算もしてくれる。


    sqlite> select time('09:05:00', '+1 minutes');
    09:06:00

    sqlite> select time('09:05:00', '-1 minutes');
    09:04:00

    sqlite> select time('09:59:00', '+2 minutes');
    10:01:00

    sqlite> select time('10:01:00', '-2 minutes');
    09:59:00

    sqlite> select datetime('2021-01-01 09:05:00', '+1 minutes');
    2021-01-01 09:06:00

    sqlite> select datetime('2021-01-01 09:05:00', '-1 minutes');
    2021-01-01 09:04:00

秒（seconds）とミリ秒（milliseconds）の計算

time()やdatetime()の引数にNNN.NNNN secondsを追加する。
例えば１秒後を計算したければ+1 secondsを追加する。

分を跨ぐ計算もしてくれる。

なお、ミリ秒は切り捨てられる。


    sqlite> select time('09:00:05', '+1 seconds');
    09:00:06

    sqlite> select time('09:00:05', '-1 seconds');
    09:00:04

    sqlite> select datetime('09:00:05', '+1 seconds');
    2000-01-01 09:00:06

    sqlite> select datetime('09:00:05', '-1 seconds');
    2000-01-01 09:00:04

    sqlite> select time('09:00:59', '+2 seconds');
    09:01:01

    sqlite> select time('09:00:01', '-2 seconds');
    08:59:59

    sqlite> select time('09:00:05', '+00.0001 seconds');
    09:00:05

    sqlite> select time('09:00:05.9999', '+0.0002 seconds');
    09:00:05

selectの結果で日付を計算する

上記の通り、時間を計算するにはtime()や datetime()の引数に文字列を指定する必要がある。データベース内のデータを使って計算する場合、文字列を連結して引数を作ってやればいい。

他のテーブルのデータを利用する場合はテーブルを結合する。

以下はnumカラムの値をtimeカラムの秒に足す例。

time_list テーブル
str_time text	int_num integer
09:01:01	1
09:01:01	2
09:01:01	3


    sqlite> select
    ...>     time(
    ...>         str_time
    ...>         , '+' || int_num || ' seconds'
    ...>     )
    ...> from
    ...>     time_list
    ...> ;
    09:01:02
    09:01:03
    09:01:04

時間のフォーマットをdateやdatetimeの引数に指定すると、必ず「2000年1月1日」になるので注意すること。

参考URL

SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html

2021年12月4日土曜日

SQLite3 time()のサンプル

日時の処理のまとめ

概要

time()では以下のフォーマットで日時を取り扱うことができる。各フォーマットのサンプルを記載しておく。 time()で返される値の日付部分は必ず2000年1月1日として扱われる。

フォーマット

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

Y:年、M:月、D:日、H:時、M:分、S:秒、 T:年月日と時分秒を区切る文字。

Date and Time Formats

実行例

環境

Windows 10 64bit
SQLite3 ver.3.37.0

YYYY-MM-DD

時間部分を指定しないと 0時0分0秒として取得される。
さらに指定した日付に関係なく必ず2000年1月1日として扱われる。
また、月や日が一桁の場合は必ず0埋めする。
0埋めしないと結果がnullになってしまう。


sqlite> select time('2021-11-30');
00:00:00

sqlite> select datetime(time('2021-11-30'));
2000-01-01 00:00:00

sqlite> .nullvalue [null]
sqlite> select datetime(time('2021-11-1'));
[null]

YYYY-MM-DD HH:MM
YYYY-MM-DDTHH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DDTHH:MM:SS

秒を指定しないと0秒として取得される。
「T」が有っても無くても結果は同じ。
指定した日付に関係なく2000年1月1日として扱われる、各項目一桁の時に0埋めにしないと結果がnullになるのは前述と同じ。


sqlite> select time('2021-11-30 01:01');
01:01:00

sqlite> select time('2021-11-30T01:01');
01:01:00

sqlite> select datetime(time('2021-11-30 01:01'));
2000-01-01 01:01:00

sqlite> select time('2021-01-01 01:01:01');
01:01:01

sqlite> select time('2021-01-01T01:01:01');
01:01:01

sqlite> select datetime(time('2021-01-01 01:01:01'));
2000-01-01 01:01:01

sqlite> .nullvalue [null]
sqlite> select time('2021-1-1 1:1:1');
[null]

YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM:SS.SSS

ミリ秒は切り捨てられる。
指定した日付に関係なく2000年1月1日として扱われる、各項目一桁の時に0埋めにしないと結果がnullになるのは前述と同じ。


sqlite> select time('2021-01-01 01:01:01.001');
01:01:01

sqlite> select time('2021-01-01T01:01:01.001');
01:01:01

sqlite> select datetime(time('2021-01-01T01:01:01.001'));
2000-01-01 01:01:01

sqlite> .nullvalue [null]
sqlite> select time('2021-1-1 1:1:1.1');
[null]

HH:MM
HH:MM:SS
HH:MM:SS.SSS

time()のメインとなる使い方。ミリ秒は切り捨てられる。
指定した日付に関係なく2000年1月1日として扱われる、時分秒が一桁の場合、0埋めが必要なのは他のフォーマットと同じ。



sqlite> select time('01:01');
01:01:00

sqlite> select time('01:01:01');
01:01:01

sqlite> select time('01:01:01.001');
01:01:01

sqlite> select datetime(time('01:01:01.001'));
2000-01-01 01:01:01

sqlite> .nullvalue [null]
sqlite> select time('1:1:1.1');
[null]

now

本日日付を返す。大文字でもOK。 datetime等に渡すと2000年1月1日になるのは前述と同じ。


sqlite> select time('now');
13:01:04

sqlite> select time('NOW');
13:01:20

sqlite> select datetime(time('now'));
2000-01-01 13:01:48

DDDDDDDDDD

ユリウス日を指定する。あまり使う機会は無さそう。


sqlite> select datetime(2459519.58);
2021-11-01 01:55:12

sqlite> select time(2459519.58);
01:55:12

sqlite> select datetime(time(2459519.58));
2000-01-01 01:55:12

参考サイト

SQLite 公式サイト(英語)
https://www.sqlite.org/index.html
Date And Time Functions
公式の日付操作に関するドキュメント
https://www.sqlite.org/lang_datefunc.html
あさはか備忘録 SQLite3 コマンドラインツールでnullの表示を見やすくする
https://sfnovicenotes.blogspot.com/2020/11/sqlite3-null.html