SQLite の自動タイムスタンプ

Junaid khan 2023年6月21日

SQLite SQLite Timestamp

タイムスタンプを SQLite データベースに追加する
SQLite で Datetime または Timestamp データ型を使用する
DateTime を SQLite テーブルから取得する
SQLite で日付と時刻を取得する
SQLite の日付と時刻のテキストストレージクラス

C# 言語では、SQLite データベースを使用して、データベースから値を格納および取得します。データベースには、行と列を持つさまざまなテーブルを含めることができます。テーブルに新しいレコードを追加すると、タイムスタンプが null の新しい行が追加されます。タイムスタンプが必要な場合、新しい行または特定の情報がいつテーブルに追加されたかを、日付と時刻固有の形式で確認できます。自動タイムスタンプを取得するには、Datetime データ型として情報を取得するか、別の情報として日付と時刻を取得します。

タイムスタンプを SQLite データベースに追加する

SQLite データベースにタイムスタンプを追加する最善の方法は、データベースにテーブルを作成する際に、データ型 DATETIME の Timestamp でフィールドを追加することです。 Timestamp のデータ型は、SQLite データベースでは DateTime である必要があります。

CREATE TABLE NameOfTheTable(
    myID INTEGER PRIMARY KEY,
    First_Name TEXT,
    Other FIELDS (if needed),
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

出力：

Creates the table with provided fields and last field timestamp

このテーブル内に新しいエントリがある場合、テーブルは、提供された情報と現在の時刻を含む新しい行を TimeStamp フィールドに挿入します。ただし、ユーザーが明示的に NULL 値に設定した場合、このフィールドには NULL を入力できます。

たとえば、次のコマンドを使用して、上記で作成したテーブルにさまざまな行を挿入します。

INSERT INTO NameOfTheTable(First_Name, sqltime) VALUES('case1', '2020-07-28T15:36:56.200');
INSERT INTO NameOfTheTable(First_Name, sqltime) VALUES('case2', '2020-10-28T13:40:02.200');
# here, the time value will be recorded as NULL
INSERT INTO NameOfTheTable(First_Name) VALUES('case3');

ここで、SELECT コマンドを使用して、テーブル内のすべてのレコードを表示する必要があります。

SELECT * FROM NameOfTheTable;

出力：

1:case1:2020-07-28T15:36:56.200
2:case2:2020-07-28T15:36:56.200
3:case3:2022-11-11 05:38:20

SQLite で `Datetime` または Timestamp データ型を使用する

SQLite では、レコード内の変更を追跡する必要がある場合、これらのレコードはタイムスタンプデータ型で更新されます。したがって、フィールドで特定の時刻が必要な場合は、フィールドに DateTime データ型を使用する必要があります。

UNIX タイムスタンプまたはネイティブ SQLite DateTime フィールドを比較する場合は常に DateTime データ型を選択する必要があります。これは、ネイティブ DateTime データ型で次の計算を実行できるためです。

SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)

このコマンドを使用して、DateTime の形式を UNIX タイムスタンプに変更することもできます。

SELECT UNIX_TIMESTAMP(my_datetime)

`DateTime` を SQLite テーブルから取得する

DateTime データ型を SQLite データベースに挿入すると、最初に文字列型に変換されてからテーブルに挿入されます。同様に、DateTime データ型のデータを取得すると、sqlite3 モジュールは最初にそれを文字列に変換します。

概念を理解するために例を挙げてみましょう。関数 insertQuery() は、id (テーブルに新しいレコードが追加されると主キーが自動的にインクリメントされる)、my_name (名前を格納するための空でない文字列フィールド)、そして最後に join_date (タイムスタンプデータ型形式) として 3つのパラメーターを取ります。）。

さらに、このプログラムは時刻を文字列形式ではなく、タイムスタンプ形式で返します。

import datetime
import sqlite3


def insertQuery(id, my_name, joiningDate):
    try:
        sqliteConnection = sqlite3.connect(
            "SQLite.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
        )
        cursor = sqliteConnection.cursor()
        print("SQLite Database is connected.")

        # query to create a new table with three fields
        sqlite_create_table_query = """CREATE TABLE new_table (
                                       id INTEGER PRIMARY KEY,
                                       my_name TEXT NOT NULL,
                                       joiningDate timestamp);"""

        cursor = sqliteConnection.cursor()
        cursor.execute(sqlite_create_table_query)

        # insert the new table information here
        sqlite_insert_with_param = """INSERT INTO 'new_table'
                          ('id', 'my_name', 'joiningDate')
                          VALUES (?, ?, ?);"""

        data_tuple = (id, my_name, joiningDate)
        cursor.execute(sqlite_insert_with_param, data_tuple)
        sqliteConnection.commit()
        print("New record added successfully \n")

        # to get the timestamp information from the table
        sqlite_select_query = (
            """SELECT my_name, joiningDate from new_table where id = ?"""
        )
        cursor.execute(sqlite_select_query, (1,))
        records = cursor.fetchall()

        # the return type of datetime is not string now.
        for row in records:
            developer = row[0]
            joining_Date = row[1]
            print(developer, " joined on", joiningDate)
            print("Date type is ", type(joining_Date))

        cursor.close()

    # if an exception is found in sqlite version 3.
    except sqlite3.Error as error:
        print("Error with SQLite database", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("Closed connection for SQLite database.")


insertQuery(0, "NewName", datetime.datetime.now())

出力：

SQLite Database is connected.
New record added successfully

NewName  joined on 2019-06-28 20:57:32.352790
Date type is <class 'datetime.datetime'>
Closed connection for SQLite database.

SQLite で日付と時刻を取得する

Unix タイムスタンプの場合、SQLite の関数 DATETIME() を引数 unixepoch とともに使用して、実際の日付と時刻を計算できます。または、DATE() 関数を使用して日付情報のみを取得し、TIME() 関数を使用して時間セクション部分を返すこともできます。

日付と時刻を取得する

SELECT DATETIME(1793956207, 'unixepoch');

出力：

2022-11-06 10:09:03

日付を取得

SELECT DATE(1793956207, 'unixepoch');

出力：

2022-11-06

時間を取得

SELECT TIME(1793956207, 'unixepoch');

出力：

10:09:03

SQLite の日付と時刻のテキストストレージクラス

SQLite では、組み込みメソッドを使用してデータベース内の日付と時刻を処理します。それらと対話するための組み込みクラスがある SQLite とは異なり、SQLite データベースには、それらをサポートするための日付と時刻の組み込み関数があります。

SQLite データベースで日付と時刻を設定するために TEXT ストレージクラスを使用する場合、次の形式を使用する必要があります。

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

この形式は 2019-02-02 11:21:08.321 になります。