SQLiteの日付時刻型徹底解説：TEXT、NUMERIC、DATETIME、TIMESTAMP比較

SQLiteで日付時刻値を格納および取得するには、いくつかの方法があります。ここでは、最も一般的な方法をいくつかご紹介します。

TEXT形式で格納

最も簡単な方法は、日付時刻値をTEXT形式で格納することです。この方法では、文字列として値を格納するため、形式の変換が必要になります。

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value TEXT
);

INSERT INTO my_table (datetime_value) VALUES ('2024-05-22 06:03:00');

SELECT datetime_value FROM my_table;

このコードは、my_tableという名前のテーブルを作成し、datetime_valueという名前の列を追加します。この列はTEXT型で、日付時刻値を文字列として格納します。

INSERTステートメントを使用して、テーブルに値を挿入します。このステートメントは、datetime_value列に2024-05-22 06:03:00という値を挿入します。

NUMERIC形式で格納

日付時刻値をNUMERIC形式で格納することもできます。この方法では、値をUNIXタイムスタンプとして格納します。UNIXタイムスタンプは、1970年1月1日0時0分0秒からの経過秒数を表す数値です。

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value INTEGER
);

INSERT INTO my_table (datetime_value) VALUES (1653187380);

SELECT datetime_value FROM my_table;

INSERTステートメントを使用して、テーブルに値を挿入します。このステートメントは、datetime_value列に1653187380という値を挿入します。この値は、2024年5月22日06時03分00秒に対応するUNIXタイムスタンプです。

DATETIME形式で格納

日付時刻値をDATETIME形式で格納することもできます。この方法は、値をISO 8601形式で格納します。ISO 8601形式は、日付と時刻を表現するための国際標準です。

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value DATETIME
);

INSERT INTO my_table (datetime_value) VALUES ('2024-05-22 06:03:00');

SELECT datetime_value FROM my_table;

TIMESTAMP形式で格納

日付時刻値をTIMESTAMP形式で格納することもできます。この方法は、値をSQLite独自の形式で格納します。TIMESTAMP形式は、DATETIME形式に似ていますが、ミリ秒までの精度が追加されています。

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value TIMESTAMP
);

INSERT INTO my_table (datetime_value) VALUES ('2024-05-22 06:03:00.000');

SELECT datetime_value FROM my_table;

SQLiteで日付時刻値を格納および取得するサンプルコード

TEXT形式で格納

import sqlite3

# データベースへの接続
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# テーブルの作成
cursor.execute('''
CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value TEXT
);
''')

# データの挿入
cursor.execute("INSERT INTO my_table (datetime_value) VALUES ('2024-05-22 06:03:00')")

# データの取得
cursor.execute("SELECT datetime_value FROM my_table")
results = cursor.fetchall()

# 結果の表示
for row in results:
  print(row[0])

# データベースのクローズ
conn.close()

NUMERIC形式で格納

import sqlite3

# データベースへの接続
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# テーブルの作成
cursor.execute('''
CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value INTEGER
);
''')

# タイムスタンプの取得
import time
timestamp = int(time.time())

# データの挿入
cursor.execute("INSERT INTO my_table (datetime_value) VALUES (?)", (timestamp,))

# データの取得
cursor.execute("SELECT datetime_value FROM my_table")
results = cursor.fetchall()

# 結果の表示
for row in results:
  # UNIXタイムスタンプを日付時刻に変換
  from datetime import datetime
  dt_object = datetime.fromtimestamp(row[0])
  print(dt_object.strftime('%Y-%m-%d %H:%M:%S'))

# データベースのクローズ
conn.close()

DATETIME形式で格納

import sqlite3

# データベースへの接続
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# テーブルの作成
cursor.execute('''
CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value DATETIME
);
''')

# データの挿入
from datetime import datetime
dt_object = datetime.now()
datetime_value = dt_object.strftime('%Y-%m-%d %H:%M:%S')

cursor.execute("INSERT INTO my_table (datetime_value) VALUES (?)", (datetime_value,))

# データの取得
cursor.execute("SELECT datetime_value FROM my_table")
results = cursor.fetchall()

# 結果の表示
for row in results:
  print(row[0])

# データベースのクローズ
conn.close()

TIMESTAMP形式で格納

import sqlite3

# データベースへの接続
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# テーブルの作成
cursor.execute('''
CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datetime_value TIMESTAMP
);
''')

# データの挿入
from datetime import datetime
dt_object = datetime.now()
datetime_value = dt_object.strftime('%Y-%m-%d %H:%M:%S.%f')

cursor.execute("INSERT INTO my_table (datetime_value) VALUES (?)", (datetime_value,))

# データの取得
cursor.execute("SELECT datetime_value FROM my_table")
results = cursor.fetchall()

# 結果の表示
for row in results:
  print(row[0])

# データベースのクローズ
conn.close()

これらのコード例は、SQLiteで日付時刻値を格納および取得する方法を示しています。

• 各例では、my_tableという名前のテーブルを作成します。このテーブルには、idという主キー列と、datetime_valueという名前の日付時刻値列が含まれています。
• 各例では、datetime_value列に異なる形式の日付時刻値を挿入します。
• 各例では、datetime_value列から値を取得し、コンソールに表示します。

これらの例は、基本的な使用方法を示すものです。SQLiteで日付時刻値を扱う際には、さまざまな方法があります。詳細については、SQLiteのドキュメントを参照してください。

補足

• 上記のコードは、Python 3.x を使用して記述されています。他のプログラミング言語を使用している場合は、適切なライブラリと

SQLiteで日付時刻値を格納および取得するその他の方法

strftime() 関数を使用して、日付時刻値を任意の形式で文字列に変換することができます。

SELECT strftime('%Y-%m-%d %H:%M:%S', datetime_value) FROM my_table;

このクエリは、my_table テーブルの datetime_value 列のすべての値を、YYYY-MM-DD HH:MM:SS 形式の文字列に変換して返します。

Julian Day Number は、西暦紀元1年1月1日0時0分0秒からの経過日数を表す数値です。日付時刻値を Julian Day Number に変換することで、計算や比較を簡単に行うことができます。

SELECT julianday('2024-05-22 06:03:00')

このクエリは、2024-05-22 06:03:00 という日付時刻値を Julian Day Number に変換して返します。

外部ライブラリを使用する

SQLiteには、日付時刻値を扱うための組み込み関数がありますが、機能が限られています。より多くの機能が必要な場合は、外部ライブラリを使用することができます。

これらのライブラリは、日付時刻値の形式変換、時差の計算、日付操作など、さまざまな機能を提供しています。

• 単純に日付時刻値を格納して取得するだけの場合、最も簡単な方法である TEXT 形式を使用するのがよいでしょう。
• 日付時刻値を形式変換したり、計算したりする必要がある場合は、strftime() 関数や Julian Day Number を使用する方がよいでしょう。
• より多くの機能が必要な場合は、外部ライブラリを使用するのがよいでしょう。