SQLite 備忘録: 日付型

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

2026年4月26日日曜日

SQLite 3 で月単位の計算をする

日時の処理のまとめ

概要

SQLite にはデータ型として日付型がないため、日付の計算は少し特殊な書き方をする必要がある。
この記事では「月」単位の計算についてまとめる。

実行例

環境

Windows 10
sqlite Command-Line Shell ver 3.51.3

単純な日付型の加減算では計算出来ない

date()や datetime()の加減算では計算出来ず、以下のような結果になってしまう。
どうやら最初の「年」の箇所だけを数値として取り出して計算しているため、このままでは月単位の計算はできない。


sqlite> select date('2026-05-01') - date('2026-03-01');
0
sqlite> select date('2025-01-31') - date('2022-12-31');
3
sqlite> select date('2026-05-01') - 1;
2025

月を加算/減算する

月単位の加算/減算をする場合はdate()や datetime()の引数にNNN monthsを追加する。例えば１年後を計算したければ+1 monthsを追加する。


sqlite> select date('2026-04-01', '+1 months');
2026-05-01

sqlite> select date('2026-04-01', '-1 months');
2026-03-01

sqlite> select datetime('2026-04-01 09:00:00', '+1 months');
2026-05-01 09:00:00

sqlite> select datetime('2026-04-01 09:00:00', '-1 months');
2026-03-01 09:00:00

例えばこのようなテーブルの各行の１か月前、１か月後を計算するには以下のようなSQLになる。

user_table
user_name	expiration_date
hoge1	2026-04-15
hoge2	2026-12-31
hoge3	2024-02-29


select
    user_name
    , expiration_date
    , date(expiration_date, '+1 months') as next_month
    , date(expiration_date, '-1 months') as prev_month
from
    user_table
;

結果
user_name	expiration_date	next_month	prev_month
hoge1	2026-04-15	2026-05-15	2026-03-15
hoge2	2026-05-31	2026-07-01	2026-05-01
hoge3	2024-02-29	2024-03-29	2024-01-29

5月31日に +1か月した場合、6月31日は存在しないため、オーバーフローして7月1日が返されるっぽい。
1か月前も同じような理屈になり、4月30日が返されるわけではないので注意が必要。

２つの日付間の月数を計算する

大きく分けて2パターン。

１つ目は、strftime()関数を使って年と月の部分を取り出して計算する方法。
２つ目は、julianday()関数を使って日数に変換してから月数に変換する方法。

１つ目の方法は、単純に年と月の部分を取り出して計算する方法。
年部分の差に12（か月）をかけ、月の差を足せばいい。


sqlite> select
   ...>     ( strftime('%Y', '2026-01-05') - strftime('%Y', '2025-12-30') ) * 12
   ...>     + ( strftime('%m', '2026-01-05') - strftime('%m', '2025-12-30') )
   ...> ;
1

ただし、この方法は単純に年の部分だけを取り出して計算するため、日数が考慮されない。
例えば上記のSQLでは、2026年1月5日と2025年12月30日の差は1か月と計算されるが、実際には6日の差しかない。

２つ目の方法は、日付を日数に変換してから月数に変換する方法。
例えば以下のようなSQLになる。


sqlite> select (julianday('2026-01-05') - julianday('2025-12-30')) / 30;
0.2

この方法では、日付を日数に変換してから月数に変換するため、日数が考慮される。
例えば上記のSQLでは、2026年1月5日と2025年12月30日の年数の差は0.2か月と計算される。（厳密に31日の月や2月の日数を計算しようとするとさすがに面倒なので、ここでは1か月=30日で計算している）
必要に応じて小数点以下を切り捨てるなどの処理を行うと良い。


sqlite> select cast((julianday('2026-01-05') - julianday('2025-12-30')) / 30 as int);
0

SQLite 3 で二つの日付間の日数を計算する

参考URL

SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html
SQLite備忘録：日時の処理のまとめ
https://sfnovicenotes.blogspot.com/2022/01/sqlite3_02064292495.html
SQLite備忘録: SQLite 3 で日付型の加減算を行う
https://sfnovicenotes.blogspot.com/2021/12/sqlite3_18.html
SQLite備忘録: SQLite 3 で二つの日付間の日数を計算する
https://sfnovicenotes.blogspot.com/2023/01/sqlite3.html

2026年4月18日土曜日

SQLite 3 で年数単位の計算をする

日時の処理のまとめ

概要

SQLite にはデータ型として日付型がないため、日付の計算は少し特殊な書き方をする必要がある。
この記事では「年」単位の計算についてまとめる。

実行例

環境

Windows 10
sqlite Command-Line Shell ver 3.51.3

単純な日付型の加減算では計算出来ない

date()や datetime()の加減算では計算出来ず、以下のような結果になってしまう。
どうやら最初の「年」の箇所だけを数値として取り出して計算しているらしいが、正直モヤっとする。


sqlite> select date('2026-05-01') - date('2026-03-01');
0
sqlite> select date('2025-01-31') - date('2022-12-31');
3
sqlite> select date('2026-05-01') - 1;
2025

年を加算/減算する

上記の式で納得できるのであれば、それでOK。

ただ、コードの保守性や引継ぎを考えると「ここは年の計算をしている」ことがコードからわかることがベスト。

年単位の加算/減算をする場合はdate()や datetime()の引数にNNN yearsを追加する。例えば１年後を計算したければ+1 yearsを追加する。


sqlite> select date('2026-04-01', '+1 years');
2027-04-01

sqlite> select date('2026-04-01', '-1 years');
2025-04-01

sqlite> select datetime('2026-04-01 09:00:00', '+1 years');
2027-04-01 09:00:00

sqlite> select datetime('2026-04-01 09:00:00', '-1 years');
2025-04-01 09:00:00

例えばこのようなテーブルの各行の１年後、１年前を計算するには以下のようなSQLになる。

user_table
user_name	expiration_date
hoge1	2026-04-15
hoge2	2026-12-31
hoge3	2024-02-29


select
    user_name
    , expiration_date
    , date(expiration_date, '+1 years') as next_year
    , date(expiration_date, '-1 years') as prev_year
from
    user_table
;

結果
user_name	expiration_date	next_year	prev_year
hoge1	2026-04-15	2027-04-15	2025-04-15
hoge2	2026-12-31	2027-12-31	2025-12-31
hoge3	2024-02-29	2023-03-01	2025-03-01

うるう年の2月29日に年単位の加算・減算をした結果、その年がうるう年でない場合は2月28日ではなく 3月1日になるので注意すること。

２つの日付間の年数を計算する

大きく分けて2パターン。

１つ目は、strftime()関数を使って年の部分だけを取り出して計算する方法。
２つ目は、julianday()関数を使って日数に変換してから年数に変換する方法。

１つ目の方法は、単純に年の部分だけを取り出して計算する方法。
例えば以下のようなSQLになる。


sqlite> select strftime('%Y', '2026-05-01') - strftime('%Y', '2025-11-01') as span_year;
1

ただし、この方法は単純に年の部分だけを取り出して計算するため、月や日が考慮されない。
例えば上記のSQLでは、2026年5月1日と2025年11月1日の年数の差は1年と計算されるが、実際には6ヶ月の差しかない。

２つ目の方法は、日付を日数に変換してから年数に変換する方法。
例えば以下のようなSQLになる。


sqlite> select (julianday('2026-05-01') - julianday('2026-03-01')) / 365.25;
0.167008898015058

この方法では、日付を日数に変換してから年数に変換するため、月や日が考慮される。
例えば上記のSQLでは、2026年5月1日と2026年3月1日の年数の差は0.16年と計算される。（厳密にうるう年を計算しようと思うと大変なのでここでは0.25日を使う。）
必要に応じて小数点以下を切り捨てるなどの処理を行うと良い。


sqlite> select cast((julianday('2026-05-01') - julianday('2026-03-01')) / 365.25 as int);
0
sqlite> select cast((julianday('2026-05-31') - julianday('2025-04-30')) / 365.25 as int);
1

SQLite 3 で二つの日付間の日数を計算する

参考URL

SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html
SQLite備忘録：日時の処理のまとめ
https://sfnovicenotes.blogspot.com/2022/01/sqlite3_02064292495.html
SQLite備忘録: SQLite 3 で日付型の加減算を行う
https://sfnovicenotes.blogspot.com/2021/12/sqlite3_18.html
SQLite備忘録: SQLite 3 で二つの日付間の日数を計算する
https://sfnovicenotes.blogspot.com/2023/01/sqlite3.html

2026年3月28日土曜日

SQLite 3 指定した日付の年・月・日部分をを取得する（strftime()）

日時の処理のまとめ

概要

日付から年・月・日を個別に取得するには、strftime()関数を使用する。

実行例

環境

Windows 11
sqlite Command-Line Shell ver 3.51.3

年を所得する。

年を取得するにはstrftime('%Y', <日付>)を使用する


sqlite> select strftime('%Y', '2026-01-31');
2026

月を取得する

月を取得するにはstrftime('%m', <日付>)を使用する


sqlite> select strftime('%m', '2026-01-31');
01

日を取得する

日を取得するにはstrftime('%d', <日付>)を使用する


sqlite> select strftime('%d', '2026-01-31');
31

参考URL

SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html
Date And Time Functions(英語)
公式のstrftime()関数の書式指定子に関するドキュメント
https://www.sqlite.org/lang_datefunc.html#strftime
SQLite 備忘録: SQLite3 年内日を求める(sfrftime('%j','yyyy-MM-dd'))
https://sfnovicenotes.blogspot.com/2022/02/sqlite3-sfrftimejyyyy-mm-dd.html
SQLite 備忘録: SQLite3 日付から曜日を取得する (strftime('%w', 'yyyy-MM-dd'))
https://sfnovicenotes.blogspot.com/2022/02/sqlite3-strftimew-yyyy-mm-dd.html
SQLite 備忘録: SQLite3 年内週を取得する (strftime('%W', 'yyyy-MM-dd'))
https://sfnovicenotes.blogspot.com/2022/02/sqlite3-strftimew-yyyy-mm-dd_0360293618.html

2023年1月30日月曜日

SQLite 3 で二つの日付間の日数を計算する

日時の処理のまとめ

概要

SQLite ではdate関数等の結果を加減算しても期待するような結果にはならない。二つの日付間の日数を計算するにはユリウス通日を利用する必要がある。

実行例

環境

Windows 10
sqlite Command-Line Shell ver 3.40.1

単純な日付型の加減算では計算出来ない

date()や datetime()の加減算では計算出来ず、以下のような結果になる。


sqlite> select date('2023-01-31') - date('2022-12-31');
1
sqlite> select datetime('2023-01-31 09:00:00') - datetime('2022-12-31 09:00:00');
1

期間(日数)を計算するにはユリウス通日を使用する

ユリウス通日(Wikipedia)


    sqlite> select julianday('2023-01-31') - julianday('2022-12-31');
    31.0

このままだと小数点が邪魔なため、cast()関数で整数に変換する。


    sqlite> select cast(julianday('2023-01-31') - julianday('2022-12-31') as integer);
    31

参考URL

SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html
SQLite備忘録: SQLite 3 で日付型の加減算を行う
https://sfnovicenotes.blogspot.com/2021/12/sqlite3_18.html

2022年3月5日土曜日

SQLite3 年の初日を取得する (start of year)

日時の処理のまとめ

概要

指定した日時からその年の初日(1月1日)を取得する方法。
文字列を切り貼りするよりはスマートに記述することが出来る。

構文

「date()」もしくは「datetime()」の引数に「start of year」を追加するとその月の初日を取得することができる。
「start of year」は大文字と小文字どちらでもいい。

「datetime()」の引数に「start of year」を指定した場合、結果の時間部分は「00:00:00」になることに注意すること。


date(time-value, 'start of year')
datetime(time-value, 'start of year')

日時として指定出来る文字列(time-value)については以下を参照のこと。

実行例

環境

Windows 10 64bit
SQLite3 ver.3.37.0


sqlite> select date('2022-03-05', 'start of year');
2022-01-01

sqlite> select datetime('2022-03-05 09:00:00', 'start of year');
2022-01-01 00:00:00

sqlite> select date('now', 'start of year');
2022-01-01

sqlite> select datetime('now', 'start of year');
2022-01-01 00:00:00

sqlite> select date(datetime('2022-03-05 09:00:00'), 'start of year');
2022-01-01

sqlite> select datetime(date('2022-03-05'), 'start of year');
2022-01-01 00:00:00

SQLite3で「現在」を取得する

外部参考サイト

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

2022年2月19日土曜日

SQLite3 年内日を求める(sfrftime('%j','yyyy-MM-dd'))

日時の処理のまとめ

概要

strftime()を使用すれば、指定した日付が年内の何日目かを取得できる。

構文

strftime()は第一引数に日時から取得する要素の変数を、第二引数に実際の日時を指定することで日時を自由な文字列フォーマットに変換して出力することができる。


strftime(format, datetime)

日時として指定出来る文字列はdate()やdatetime()に指定する文字列と同じ。

年内日を取得するにはstrftime()の第一引数に「%j」を指定する。
大文字の「%J」ではなく、小文字の「%j」を指定すること。

実行例

環境

Windows 10 64bit
SQLite3 ver.3.37.0


sqlite> select strftime('%j', '2020-12-30');
365
sqlite> select strftime('%j', date('2020-12-30'));
365
sqlite> select strftime('%j', datetime('2020-12-30'));
365
sqlite> select strftime('%j', '2020-12-31');
366
sqlite> select strftime('%j', '2021-01-01');
001
sqlite> select strftime('%j', '2021-01-31');
031
sqlite> select strftime('%j', '2021-02-01');
032

外部参考サイト

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

2022年2月12日土曜日

SQLite3 年内週を取得する (strftime('%W', 'yyyy-MM-dd'))

日時の処理のまとめ

概要

strftime()を使用すれば、指定した日付が年内の何週目かを取得できる。

1月1日が必ず第1週になるわけではないことに注意すること。

構文


strftime(format, datetime)

日時として指定出来る文字列はdate()やdatetime()に指定する文字列と同じ。

年内週を取得するにはstrftime()の第一引数に「%W」を指定する。小文字の「%w」では無く大文字の「%W」を指定すること。

実行例

環境

Windows 10 64bit
SQLite3 ver.3.37.0


sqlite> select strftime('%W', '2022-02-09');
06
sqlite> select strftime('%W', date('2022-02-09'));
06
sqlite> select strftime('%W', datetime('2022-02-09'));
06

月曜日が基準になっている

SQLiteでの年内週の数え方は1月1日以降の月曜日の数となる。

以下は2018年と2019年の比較。月曜日で+1されていることがわかる。

2018年

1月
日曜日	月曜日	火曜日	水曜日	木曜日	金曜日	土曜日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31


sqlite> select strftime('%W', '2018-01-01');
01
sqlite> select strftime('%W', '2018-01-02');
01
sqlite> select strftime('%W', '2018-01-07');
01
sqlite> select strftime('%W', '2018-01-08');
02
sqlite> select strftime('%W', '2018-01-09');
02
sqlite> select strftime('%W', '2018-01-14');
02
sqlite> select strftime('%W', '2018-01-15');
03
sqlite> select strftime('%W', '2018-01-16');
03

2019年

1月
日曜日	月曜日	火曜日	水曜日	木曜日	金曜日	土曜日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31


sqlite> select strftime('%W', '2019-01-01');
00
sqlite> select strftime('%W', '2019-01-06');
00
sqlite> select strftime('%W', '2019-01-07');
01
sqlite> select strftime('%W', '2019-01-08');
01
sqlite> select strftime('%W', '2019-01-13');
01
sqlite> select strftime('%W', '2019-01-14');
02
sqlite> select strftime('%W', '2019-01-15');
02

外部参考サイト

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

2022年2月5日土曜日

SQLite3 日付から曜日を取得する (strftime('%w', 'yyyy-MM-dd'))

日時の処理のまとめ

概要

SQLite3で日付から曜日を取得するにはstrftime()関数を使用する。

構文


strftime(format, datetime)

日時として指定出来る文字列はdate()やdatetime()に指定する文字列と同じ。

曜日を取得するにはstrftime()の第一引数に「%w」を指定する。返り値は0から6までの値となり、これが日曜日～月曜日を指す。

返り値	曜日
0	日曜日
1	月曜日
2	火曜日
3	水曜日
4	木曜日
5	金曜日
6	土曜日

実行例

環境

Windows 10 64bit
SQLite3 ver.3.37.0


sqlite> select strftime('%w', '2022-01-01');
6
sqlite> select strftime('%w', '2022-01-02');
0
sqlite> select strftime('%w', '2022-01-03');
1
sqlite> select strftime('%w', '2022-01-04');
2
sqlite> select strftime('%w', '2022-01-05');
3
sqlite> select strftime('%w', '2022-01-06');
4
sqlite> select strftime('%w', '2022-01-07');
5

曜日の名前を取得したい

strftime()関数で曜日が数字として取得出来るので、これを曜日名に直すにはCASE文などを利用して条件分岐してやればいい。

なお、strftime()の戻り値は数値型ではなく文字列型として比較すること。

sample_date
id	str_date
A	2022-01-01
B	2022-01-02
C	2022-01-03
D	2022-01-04
E	2022-01-05
F	2022-01-06
G	2022-01-07


sqlite> .mode box
sqlite> select
   ...>     id
   ...>     , case strftime('%w', str_date)
   ...>         when '0' then 'Sunday'
   ...>         when '1' then 'Monday'
   ...>         when '2' then 'Tuesday'
   ...>         when '3' then 'Wednesday'
   ...>         when '4' then 'Thursday'
   ...>         when '5' then 'Friday'
   ...>         when '6' then 'Saturday'
   ...>         else 'undefined'
   ...>     end as weekday
   ...> from
   ...>     sample_date
   ...> ;
┌────┬───────────┐
│ id │  weekday  │
├────┼───────────┤
│ A  │ Saturday  │
│ B  │ Sunday    │
│ C  │ Monday    │
│ D  │ Tuesday   │
│ E  │ Wednesday │
│ F  │ Thursday  │
│ G  │ Friday    │
└────┴───────────┘

外部参考サイト

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

2022年1月29日土曜日

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

ver 3.35.3 時点

外部参考サイト

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

2022年1月22日土曜日

SQLite3では日時は文字列型で保存する

日時の処理のまとめ

概要

SQLite3のカラムのデータ型にはDate型やDatetime型が無いので、データベースには文字列型として登録する。

日付の大小を比較するのであれば文字列のまま比較すればいいが、日付の計算をしたい場合はdate()やdatetime()といった関数を使用する。

Datatypes In SQLite (SQLite 公式のデータ型に関するドキュメント)

実行例

環境

Windows 10
sqlite3 (3.27.0)

現在日時をデータベースに登録する

日付を登録するカラムは「text」型にしておく。
本日日付を「date('now')」で取得し、新規挿入する。
現在時間を「time('now')」で取得し、新規挿入する。
現在日付、時間を「datetime('now')」で取得し、新規挿入する。
selectで現在日時が登録されていることが確認できる。


sqlite> -- # 1.
sqlite> create table sample_date (
   ...>     test_date text
   ...> );

sqlite> -- # 2.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     date('now')
   ...> );

sqlite> -- # 3.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     time('now')
   ...> );

sqlite> -- # 4.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     datetime('now')
   ...> );

sqlite> -- # 5.
sqlite> select test_date from sample_date;
2022-01-18
13:47:39
2022-01-18 13:47:40

特定の日付を登録する

文字列として登録すればいいのだが、後で「date()」「datetime()」で使用するために SQLite3の日時フォーマットにしたがった文字列にしたほうがいい。

日時フォーマットについては以下の記事を参照のこと。

スラッシュ区切りなんかで登録してしまうと後々面倒なことになる。

sqlite3でスラッシュ区切りの日付を扱う

以下は特定の日時を登録する実行例。

日付を登録するカラムは「text」型にしておく。
「2022年1月1日」を新規挿入する。
「9時9分9秒」を新規挿入する。
「2022年1月1日9時9分9秒」を新規挿入する。


sqlite> -- # 1.
sqlite> create table sample_date (
   ...>     test_date text
   ...> );

sqlite> -- # 2.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '2022-01-01'
   ...> );

sqlite> -- # 3.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '09:09:09'
   ...> );

sqlite> -- # 4.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '2022-01-01 09:09:09'
   ...> );

sqlite> -- # 5.
sqlite> select test_date from sample_date;
2022-01-01
09:09:09
2022-01-01 09:09:09

日時を比較する

日時を比較する場合、フォーマットがあっていれば文字列として比較することもできる。

万全を期すのであればdate()やdatetime()といった関数で日付型にしてから比較したほうがいい。
「YYYY-MM-DD HH:MM:SS」と「YYYY-MM-DDTHH:MM:SS」といったフォーマットが違う日付データを文字列として比較すると、意図しない結果になることがある。

以下は日時比較の実行例

フォーマットが同じなら文字列として比較出来る。
date()やdatetime()関数で変換してからも比較出来る。
フォーマットが違う場合はdate()やdateitme()関数を使うこと。


sqlite> -- # 1.
sqlite> select '2022-01-01 09:00:00' > '2022-01-01 08:59:59';
1
sqlite> select '2022-01-01 09:00:00' > '2022-01-01 10:01:01';
0
sqlite> select '2022-01-01 09:00:00' = '2022-01-01 09:00:00';
1

sqlite> -- # 2.
sqlite> select datetime('2022-01-01 09:00:00') > datetime('2022-01-01 08:59:59');
1

sqlite> -- # 3.
sqlite> select '2022-01-01 09:00:00' > '2022-01-01T08:59:59';
0
sqlite> select datetime('2022-01-01 09:00:00') > datetime('2022-01-01T08:59:59');
1

SQLite 3 trueとfalseの取り扱い

外部参考サイト

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月18日土曜日

SQLite3で年月日の計算をする

日時の処理のまとめ

概要

SQLiteで日付の計算をするには「date()」、もしくは「datetime()」を使用する。

構文


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

実行例

環境

Windows 10 64bit
SQLite3 ver.3.36.0

年の計算

「date()」や「datetime()」の引数に「NNN years」を追加する。
例えば１年後を計算したければ「+1 years」を追加する。


sqlite> select date('2021-01-01', '+1 years');
2022-01-01

sqlite> select date('2021-01-01', '-1 years');
2020-01-01

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

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

「0000年」や「9999年」を計算すると？

「0000年」からさらに年数を引くと紀元前の文字列が取得出来る。

通常「date()」の計算結果はさらに「date()」の引数として指定することが出来るが、 0000年からさらに年数を引いた計算結果を「date()」に入れると「null」になってしまう。

紀元前の計算をしたいのであればユリウス日を使うようにする。


sqlite> .nullvalue [null]
sqlite> select date('0000-01-01', '-1 years');
-001-01-01

sqlite> select date(date('0000-01-01', '-1 years'), '-1 years');
[null]

sqlite> select date(date('0003-01-01', '-1 years'), '-1 years');
0001-01-01

「9999年」にさらに年数を足すことは出来ない

「9999年」にさらに年数を足しても結果は必ず「null」になる。


sqlite> .nullvalue [null]
sqlite> select date('9999-01-01', '+1 years');
[null]

月の計算

「date()」や「datetime()」の引数に「NNN months」を追加する。
例えば１か月後を計算したければ「+1 months」を追加する。

年を跨ぐ場合もちゃんと計算される。


sqlite> select date('2021-01-01', '+1 months');
2021-02-01

sqlite> select date('2021-01-01', '-1 months');
2020-12-01

sqlite> select date('2021-12-01', '+1 months');
2022-01-01

sqlite> select date('2021-01-01', '+13 month');
2022-02-01

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

sqlite> select datetime('2021-12-01 09:00:00', '-1 months');
2021-11-01 09:00:00

日の計算

「date()」や「datetime()」の引数に「NNN day」を追加する。
例えば１日後を計算したければ「+1 day」を追加する。

年や月を跨ぐ場合もちゃんと計算される。


sqlite> select date('2021-01-01', '+1 day');
2021-01-02

sqlite> select date('2021-01-01', '-1 day');
2020-12-31

sqlite> select date('2021-12-31', '+1 day');
2022-01-01

sqlite> select date('2021-01-01', '+366 day');
2022-01-02

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

sqlite> select datetime('2021-01-01 09:00:00', '-1 day');
2020-12-31 09:00:00

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

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

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

以下は「num」カラムの値を「date」カラムの日に足す例。

date_list テーブル
str_date text	int_num integer
2021-01-01	1
2021-01-01	2
2021-01-01	3


sqlite> select
   ...>     date(
   ...>         str_date
   ...>         , '+' || int_num || ' day'
   ...>     )
   ...> from
   ...>     date_list
   ...> ;
2021-01-02
2021-01-03
2021-01-04

外部参考サイト

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

2021年12月11日土曜日

SQLite3で「現在」を取得する

日時の処理のまとめ

概要

SQLite3で「現在」を取得する方法はいくつかある。
いわゆる「タイムスタンプ」を取得したい場合は、以下に示すいずれかの方法で取得すればいい。
全て UTC で取得されるので必要に応じて時差を計算する必要がある。

now

date()やtime()といった日時用の関数の引数として使用して、現在日時を取得する。


sqlite> select date('now');
2021-12-10

sqlite> select datetime('now');
2021-12-10 13:32:03

sqlite> select time('now');
13:32:10

current_date

本日日付(UTC)を取得出来る。date('now')と同等の機能。
datetime()の引数に指定した場合、時刻部分は00:00:00となる。


sqlite> select current_date;
2021-12-10

sqlite> select datetime(current_date);
2021-12-10 00:00:00

current_time

現在時刻(UTC)を取得出来る。time('now')と同等の機能。
datetime()の引数に指定した場合、日付部分は2000-01-01となる。


sqlite> select current_time;
14:17:59

sqlite> select datetime(current_time);
2000-01-01 14:18:10

current_timestamp

現在日時(UTC)を取得出来る。datetime('now')と同等の機能。


sqlite> select current_timestamp;
2021-12-10 14:18:35

データ作成日のデフォルト値としてよく使う。


create table users {
    user_id     text not null
    , user_name text not null
    , timestamp  text default current_timestamp
}

外部参考サイト

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

2019年6月10日月曜日

sqlite3でスラッシュ区切りの日付を扱う

日時の処理のまとめ

概要

SQLiteでYYYY/MM/DDなどのスラッシュ区切りの日付を何とかdate型にする方法のメモ。

完全にゴリ押し。
もっとスマートなやり方を知っている方教えてください。
(DBにinsertする前に整形しとけ、というのはごもっとも)

sqlite3では以下の理由で日付の扱いが少々面倒になっている。

データ型に日付型がない、日付は文字列として登録する。
日付の計算をするには date() や datetime() 関数を使い文字列 ⇒ 日付に変換する必要がある。
- SQLite3では日時は文字列型で保存する
date() や datetime() で引数に指定できるのは YYYY-MM-DD の形式。
- SQLite3 date()のサンプル
- SQLite3 datetime()のサンプル

YYYY/MM/DD のようにスラッシュ区切りの日付を扱うには以下のようなことをする。

substr() や instr() を使い YYYY/MM/DD の文字列を YYYY-MM-DD に整形。
date() や datetime() 関数を使って文字列 ⇒ 日付に変換する。

環境

Windows 10
sqlite3 (3.24.0)

2019/4/1 の文字列を日付形式のデータに変換するサンプル

                        
select
    date(
        sub.sub_Y
        || '-' 
        || substr('00' || sub.sub_M, -2, 2)
        || '-'
        || substr('00' || sub.sub_D, -2, 2)
    )
from
    (
    select
        -- 年(YEAR) -----------------------------
        substr(
            '2019/4/1'
            , 1
            , instr('2019/4/1', '/')-1	
        ) as sub_Y
        
        -- 月(MONTH) ----------------------------
        , substr(
            substr(
                '2019/4/1'
                , instr('2019/4/1', '/')+1
            )
            , 1
            , instr(
                substr(
                    '2019/4/1'
                    , instr('2019/4/1', '/')+1
                )
                , '/'
            )-1
        ) as sub_M
        
        -- 日(DAY) ------------------------------
        , substr(
            substr(
                '2019/4/1'
                , instr('2019/4/1', '/')+1
            )
            , instr(
                substr(
                    '2019/4/1'
                    , instr('2019/4/1', '/')+1
                )
                , '/'
            )+1
        ) as sub_D
) sub

外部参考サイト

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

登録: 投稿 (Atom)