SQLite3データベースでタイムスタンプ列を格納可能なINTEGER型とTEXT型の違いは

SQLite3データベースのタイムスタンプ列の型について公式ドキュメントによると、SQliteの組み込みの日付と時刻関数を使ってTEXT, REAL, INTEGERの３つの型に値を保存することができるとなっています。

参考にした公式ドキュメント

• 1-1 time --- 時刻データへのアクセスと変換 : localtime(), mktime()
  
• 1-1 datetime --- 基本的な日付型および時間型 :datetime.fromisoformat(date_string)
• 2-1 Datatypes In SQLite :2.2. Date and Time Datatype
• 2-2 Date And Time Functions : 1.datetime(), strftime(), 3. Modifiers, 4. Examples

こちらは上記 2-1の [2.2. Date and Time Datatype] の抜粋 ※改行をいれています

SQLite does not have a storage class set aside for storing dates and/or times.
Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

・TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
・REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
・INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

この記事では上記の３つの型のうちINTEGER型とTEXT型を使った場合のデータの生成方法と選択クエリーの違いを解説します。

この記事で扱うデータは下記画像に示した気象センサー表示システムで収集されたデータベースになります。

• Raspberry Pi zero (headless OS: Raspbian GNU/Linux 10 (buster))
  UDPモニターアプリはシステムサービスとして稼働し2222番のUDPポートをリッスンしています
  (1) ESP気象センサーが発信するUDPパケット(10分毎)を受信しSQLite3データベースに記録
  (2) 測定時刻 (タイムスタンプ)をINTEGERで保存
  

• ラズパイは気象データダウンロード機能(Flaskアプリ)を提供しています。
  Flaskアプリはシステムサービスとして稼働し8080番ポートをリッスンしています
  

上記システムの概要とソースコードは下記GitHubリポジトリでご覧になれます
GitHub(pipito-yukio) ラズベリーパイによる家庭用気象データ監視システム

記事のスクリプトの実行環境と前提条件

• OS: Ubuntu 22.04.3 LTS
• Python仮想環境を作成する
  [Python version] python 3.10.12 ※ 3.7以上

1. Python仮想環境の作成

• Python仮想環境作成 (例) 仮想環境名を "py_sqlite3" とする例
  ※SQLite3データベースの登録・検索処理は標準ライブラリのみで対応可能です

$ python3 -m venv py_sqlite3
# 仮想環境に入りpipをアップテーとする
$ . py_sqlite3/bin/activate
(py_sqlite3) $ pip install -U pip

3.テーブル定義とデータベース作成

3-1 (1). 測定時刻にINTEGERを使った定義

[sql/weather_db.sql]

-- センサーデバイス名テーブル
CREATE TABLE IF NOT EXISTS t_device(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR UNIQUE NOT NULL
);
-- 気象データテーブル
CREATE TABLE IF NOT EXISTS t_weather(
    did INTEGER NOT NULL,
    measurement_time INTEGER NOT NULL,
    temp_out real,
    temp_in real,
    humid real,
    pressure real,
    PRIMARY KEY (did, measurement_time),
    FOREIGN KEY (did) REFERENCES t_devices (id) ON DELETE CASCADE
);

3-1 (2). 測定時刻にTEXTを使った定義

[sql/weather_texttime_db.sql] ※t_deviceは同じなので割愛

CREATE TABLE IF NOT EXISTS t_weather(
    did INTEGER NOT NULL,
    measurement_time TEXT NOT NULL,
    temp_out real,
    temp_in real,
    humid real,
    pressure real,
    PRIMARY KEY (did, measurement_time),
    FOREIGN KEY (did) REFERENCES t_devices (id) ON DELETE CASCADE
);

3-2.データベースの作成

※下記コマンドでデータベースを生成する方法はインストーラーではよく使われます

• 測定時刻がINTEGER型
• 気象観測デバイスの登録 (デバイス名='esp8266_1')

~/db$ sqlite3 weather_db < weather_db.sql
~/db$ echo "INSERT INTO t_device VALUES(1,'esp8266_1');" | sqlite3 weather_db

• 測定時刻がTEXT型 ※気象観測デバイスの登録は割愛

~/db$ sqlite3 weather_texttime_db < weather_texttime_db.sql

【参考】以下は上記ラズパイのインストーラー (SQLite3インストールとデータベース作成)

#!/bin/bash

# execute before export my_passwd=xxxxxx

# add application environ values to .bashrc
cat ~/work/add_env_in_bashrc.txt >> ~/.bashrc

echo $my_passwd | sudo --stdin apt update && sudo apt -y upgrade
# headless os not installed.
echo $my_passwd | sudo --stdin apt -y install python3-venv sqlite3 pigpio i2c-tools tree

# Create Virtual Python environment.
mkdir py_venv
cd py_venv
python3 -m venv py37_pigpio
. py37_pigpio/bin/activate
pip install -U pip
pip install -r ~/work/py_venv/requirements_pigpio.txt
deactivate
cd ~/

# load PATH_WEATHER_DB
. ~/work/add_env_in_bashrc.txt
# Create weather database and tables by SQLite3
echo "Database file: $PATH_WEATHER_DB"
sqlite3 $PATH_WEATHER_DB < ~/db/weather_db.sql

# ...以下省略...

上記インストーラーのスクリプトのソースは以下でご覧になれます。
GitHub: home_weather_sensors: raspi_pigpio/inst_main.sh

4.測定時刻の生成方法

以下は PyCharm Community Edition for Linux版 の Python コンソールで実行した結果

4-1. Unix time を生成する方法

• A. timeモジュールの localtime関数とmktime関数を使って生成する
  (1) localtime関数はローカル時間(JST)のstruct_timeタブルを生成する
  (2) mktime( localtime )で unix timeを生成する

>>> import time
>>> local_time: time.struct_time = time.localtime()
>>> local_time
time.struct_time(tm_year=2023, tm_mon=11, tm_mday=1, tm_hour=9, tm_min=8, tm_sec=23, tm_wday=2, tm_yday=305, tm_isdst=0))
>>> unix_timestamp: float = time.mktime(local_time)
>>> unix_timestamp
1698797303.0
>>> s_local_time: str = time.strftime('%Y-%m-%d %H:%M:%S', local_time)
>>> s_local_time
'2023-11-01 09:08:23'

• B. timeモジュールの time関数を使って生成する(unix time)
  ※こちらのほうが精度が良いが、この精度まで必要ないので実際には採用しなかった

>>> unix_time: float = time.time()
>>> unix_time
1698797965.1384678

>>> # こちらはdatetime.fromtimestampでしか確認できない
>>> from datetime import datetime
>>> dt_time: datetime = datetime.fromtimestamp(unix_time)
>>> dt_time
datetime.datetime(2023, 11, 1, 9, 19, 25, 138468)
>>> s_time: str = dt_time.strftime("%Y-%m-%d %H:%M:%S")
>>> s_time
'2023-11-01 09:19:25'

4-2. SQLite3のISO8601形式の文字列を生成する方法

• datetime.isoformat(sep=' ', timespec='milliseconds') で条件を満たす文字列を生成できます
  (公式ドキュメント datetime.isoformat https://docs.python.org/ja/3/library/datetime.html#datetime.datetime.isoformat と timespec を参照)

>>> from datetime import datetime
>>> now: datetime = datetime.now()
>>> now
datetime.datetime(2023, 11, 1, 9, 16, 38, 294581)
>>> s_timestamp: str = now.isoformat(sep=' ', timespec='milliseconds')
>>> s_timestamp
'2023-11-01 09:16:38.294'

5.観測データの登録処理

5-0. 定数定義、共通の処理関数の定義

# SQL定義
FIND_DEVICE: str = "SELECT id FROM t_device WHERE name = ?"
INSERT_WEATHER: str = """
INSERT INTO t_weather(did, measurement_time, temp_out, temp_in, humid, pressure) 
 VALUES (?, ?, ?, ?, ?, ?)  
"""

# UDPのバケットはbyteから文字列に変換するため、観測データを浮動小数点数に経関する
def to_float(s_value: str) -> Optional[float]:
    try:
        val = float(s_value)
    except ValueError:
        val = None
    return val


# SQLite3 データベース接続オブジェクトを取得する
def get_connection(db_file_path: str, auto_commit: bool = False, read_only: bool = False,
                   logger: Optional[logging.Logger] = None) -> sqlite3.Connection:
    try:
        if read_only:
            db_uri = "file://{}?mode=ro".format(db_file_path)
            connection = sqlite3.connect(db_uri, uri=True)
        else:
            connection = sqlite3.connect(db_file_path)
            if auto_commit:
                connection.isolation_level = None
    except sqlite3.Error as e:
        if logger is not None:
            logger.error(e)
        raise e
    return connection


# デバイス名からデバイスID(did)を取得する
def find_device(conn: sqlite3.Connection, device_name: str,
                logger: Optional[logging.Logger] = None, log_level_debug: bool = False
                ) -> Optional[int]:
    rec: Optional[Tuple[int]]
    with conn:
        cur: sqlite3.Cursor = conn.execute(FIND_DEVICE, (device_name,))
        rec = cur.fetchone()
    if logger is not None and log_level_debug:
        logger.debug("{}: {}".format(device_name, rec))
    # https://docs.python.org/ja/3/library/sqlite3.html
    # これ以上データがない場合は Noneを返す
    if rec is not None:
        return rec[0]

    return None

5-1 (1). 測定時刻にINTEGER型を使う場合の登録処理

• 測定時刻: pythonのtimeモジュールで生成するunit time は小数点を含むためintに変換する
  ※ログ出力処理を省略しています

def insert(device_name: str, temp_out: str, temp_in: str, humid: str, pressure: str,
           measurement_time: float, logger: Optional[logging.Logger] = None) -> None:
    conn: sqlite3.Connection = get_connection(weather_db, logger=logger)
    did: Optional[int] = find_device(conn, device_name, logger=logger)
    if did is None:
        warning: str = f"{device_name} not found!"
        if logger is not None:
            logger.warning(warning)
        else:
            print(warning)
        return

    rec: Tuple[int, int, Optional[float], Optional[float], Optional[float], Optional[float]] = (
        did,
        int(measurement_time), # タイムスタンプの小数点をトリムした整数
        to_float(temp_out),
        to_float(temp_in),
        to_float(humid),
        to_float(pressure)
    )
    try:
        with conn:
            conn.execute(INSERT_WEATHER, rec)
    except sqlite3.Error as db_err:
        error: str = f"rec: {rec}\nerror:{db_err}"
        if logger is not None:
            logger.warning(error)
        else:
            print(error)
    finally:
        if conn is not None:
            conn.close()

5-1 (2). 測定時刻にTEXT型を使う場合の登録処理

• 測定時刻: iso8601形式で文字列を生成するためそのまま登録
  ※差異部分のみ掲載します

def insert(device_name: str, temp_out: str, temp_in: str, humid: str, pressure: str,
           measurement_time: str, logger: Optional[logging.Logger] = None) -> None:
    # ... 5-1 (1) と同じため割愛
    rec: Tuple[int, str, Optional[float], Optional[float], Optional[float], Optional[float]] = (
        did,
        measurement_time, #ここで文字列をそのま登録
        to_float(temp_out),
        to_float(temp_in),
        to_float(humid),
        to_float(pressure)
    )
    # ... 5-1 (1) と同じため割愛

5-2.データ登録処理

5-2-1. UDPパケットテーブルに登録する処理

• A. 測定時刻に Unit time を生成する場合
  (1) UDPパケットはバイトデータなのでUTF-8で文字列化する
  (2) カンマで分割してリストオブジェクトに格納(デバイス名,外気温,室内気温,室内湿度,気圧)
  (3) unix time を生成する ※パケット受信時間を測定時間とする
  (4) insert関数を呼び出す

def loop(client: socket.socket):
    server_ip: str = ''
    # Timeout setting
    client.settimeout(RECV_TIMEOUT)
    data: bytes
    address: str
    while True:
        try:
            data, addr = client.recvfrom(BUFF_SIZE)
            if server_ip != addr:
                server_ip = addr
                app_logger.info("server ip: {}".format(server_ip))

            # from ESP output: device_name, temp_out, temp_in, humid, pressure
            line: str = data.decode("utf-8")
            record: List[str] = line.split(",")
            # Insert weather DB with local time
            local_time: time.struct_time = time.localtime()
            curr_time: float = time.mktime(local_time)
            # これは確認用
            dt: datetime = datetime.fromtimestamp(curr_time)
            app_logger.debug(f"{curr_time} ({dt.strftime(F_DATETIME)})")
            # レコード登録
            insert(record[0], record[1], record[2], record[3], record[4],
                   measurement_time=curr_time, logger=app_logger)
        except socket.timeout as timeout:
            app_logger.warning(timeout)
            raise timeout

【参考】ラズパイ上で稼働しているレコード登録処理は下記のとおりですが、PyCharm IDEのチエックでウォーニングが出力されるため今回のコードは上記のように修正しています。
※ もちろん実行でエラーにはなりません。

# 下記処理だと def insert(*args, **kwargs)と定義する必要がある
insert(*record, measurement_time=curr_time, logger=app_logger)

• B. 測定時刻にiso8601フォーマット文字列を生成する場合
  ※差分のみを掲載します

            # Insert weather DB
            # measurement_time: ISO8601 ("YYYY-MM-DD HH:MM:SS.SSS")
            now: datetime = datetime.now()
            s_timestamp: str = now.isoformat(sep=' ', timespec='milliseconds')
            app_logger.debug(f"{s_timestamp}")
            insert(record[0], record[1], record[2], record[3], record[4],
                   measurement_time=s_timestamp, logger=app_logger)

5-2-2 UDPパケット登録処理スクリプト

• 定数定義とスクリプトパラメータ処理
  引数はデータベースファイルのパス指定だけ

import argparse
import os
import logging
import socket
import sqlite3
import time
from datetime import datetime
from typing import List, Optional, Tuple

# ログフォーマット
LOG_FMT: str = '%(levelname)s %(message)s'

# UDP受信ポート
WEATHER_UDP_PORT: int = 2222
# UDP受信バッファ
BUFF_SIZE: int = 1024
# UDP packet receive timeout 12 minutes
RECV_TIMEOUT: float = 12. * 60

if __name__ == '__main__':
    logging.basicConfig(format=LOG_FMT)
    app_logger: logging.Logger = logging.getLogger(__name__)
    app_logger.setLevel(level=logging.DEBUG)

    parser: argparse.ArgumentParser = argparse.ArgumentParser()
    # SQLite3 データベースパス
    #  for localtime:~/db/weather.db
    parser.add_argument("--sqlite3-db", type=str, required=True,
                        help="QLite3 データベースパス")
    args: argparse.Namespace = parser.parse_args()
    # データベースパス
    weather_db: str = os.path.expanduser(args.sqlite3_db)
    if not os.path.exists(weather_db):
        app_logger.warning("database not found!")
        exit(1)

• UDPソケットクライアントの生成と受信ループ呼び出し

    # Receive broadcast.
    broad_address = ("", WEATHER_UDP_PORT)
    app_logger.info(f"Listen: {broad_address}")
    # UDP client
    udp_client: socket.socket = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    app_logger.info(f"udp_client: {udp_client}")
    udp_client.bind(broad_address)
    try:
        loop(udp_client)
    except KeyboardInterrupt:
        pass
    except Exception as err:
        app_logger.error(err)
    finally:
        udp_client.close()

5-3. スクリプトの実行とログ出力

• 測定時間をINTEGER型で登録するスクリプト

(py_sqlite3) $ python UdpMonitorFromWeatherSensor.py --sqlite3-db ~/db/weather_db
INFO Listen: ('', 2222)
INFO udp_client: <socket.socket fd=3, family=AddressFamily.AF_INET, type=SocketKind.SOCK_DGRAM, proto=0, laddr=('0.0.0.0', 0)>
INFO server ip: ('192.168.0.31', 2222)
DEBUG 1698807742.0 (2023-11-01 12:02:22)
DEBUG (1, 1698807742, 15.2, 19.3, 57.6, 1008.7)
DEBUG 1698808327.0 (2023-11-01 12:12:07)
DEBUG (1, 1698808327, 15.1, 19.3, 62.3, 1008.7)

• 測定時間をTEXT型で登録するスクリプト

(py_sqlite3) $ python UdpMonitorFromWeatherSensor_textTime.py --sqlite3-db ~/db/weather_texttime_db
INFO Listen: ('', 2222)
INFO udp_client: <socket.socket fd=3, family=AddressFamily.AF_INET, type=SocketKind.SOCK_DGRAM, proto=0, laddr=('0.0.0.0', 0)>
INFO server ip: ('192.168.0.31', 2222)
DEBUG 2023-10-31 14:56:33.849
DEBUG (1, '2023-10-31 14:56:33.849', 17.2, 20.5, 49.8, 1015.4)
DEBUG 2023-10-31 15:06:16.633
DEBUG (1, '2023-10-31 15:06:16.633', 16.6, 20.5, 49.5, 1015.4)
# ...登録数が多いため省略...

6. テーブルから指定された期間のレコードを取得する処理

6-1. 選択クエリーの定義

6-1-1. 測定時間がINTEGER型(unix time)の場合

• (1) INTEGER(unix time) と、引数の日付文字列との比較をする場合
  右側の引数の日付文字列をIntegerに変換するstrftime関数を使用する必要がある

SELECT
   datetime(measurement_time, 'unixepoch', 'localtime') as measurement_time 
   ,temp_out, temp_in, humid, pressure
FROM
   t_weather
WHERE
   did=(SELECT id FROM t_device WHERE name=?)
   AND (
      measurement_time >= strftime('%s', ? ,'-9 hours')
      AND
      measurement_time <= strftime('%s', ? ,'-9 hours')
   )
ORDER BY measurement_time;

• (2) INTEGER(unix time) を日付文字列に変換した値と、日付文字列との比較をする場合
  こちらのほうがわかりやすいクエリーですが、開発当時は思い浮かびませんでした。

SELECT
   datetime(measurement_time, 'unixepoch', 'localtime') as measurement_time 
   ,temp_out, temp_in, humid, pressure
FROM
   t_weather
WHERE
   did=(SELECT id FROM t_device WHERE name=?)
   AND (
      datetime(measurement_time, 'unixepoch', 'localtime') >= ?
      AND
      datetime(measurement_time, 'unixepoch', 'localtime') <= ?
   )
ORDER BY measurement_time;

【補足】SQLite3の datetime関数とstrftime関数の働きをコマンドラインで確認します

上記の 4-1 (A) で生成した unix time を例にします

>>> local_time: time.struct_time = time.localtime()
>>> unix_timestamp: float = time.mktime(local_time)
>>> unix_timestamp
1698797303.0
>>> s_local_time: str = time.strftime('%Y-%m-%d %H:%M:%S', local_time)
>>> s_local_time
'2023-11-01 09:08:23'

• datetime関数: unix time (INTEGER)を時間文字列に変換する
  [Modifier] 'unixepoch'と'localtime' を指定します ※日付が一致しています

$ echo "SELECT datetime(1698797303, 'unixepoch', 'localtime');" | sqlite3 weather_db
2023-11-01 09:08:23

• stfftime関数: クエリーの引数(時間文字列)をINTEGER値に変換する

(A) 出力された値は 1698829703 で 1698797303 に一致しません。
　※1698829703をdatetime.strftimeで確認すると'2023-11-01 18:08:23'で9時間進んでいました。

$ echo "SELECT strftime('%s', '2023-11-01 09:08:23');" | sqlite3 weather_db
1698829703

(B) '-9 hours' を追加すると 1698797303 に一致しました。
※この関数の欠点は '-9 hours' の指定が理解しづらいことですね

$ echo "SELECT strftime('%s', '2023-11-01 09:08:23', '-9 hours');" | sqlite3 weather_db
1698797303

(関数とmodifierについては公式ドキュメント Date And Time Functions https://www.sqlite.org/lang_datefunc.html を参照)

6-1-2. 測定時間がTEXT型(iso8601形式文字列)の場合

こちらのほうが圧倒的にわかりやすいです ※列がiso8601形式文字列なので当然ですが

SELECT
   measurement_time, temp_out, temp_in, humid, pressure
FROM
   t_weather
WHERE
   did=(SELECT id FROM t_device WHERE name=?)
   AND (
      measurement_time >= ? AND  measurement_time <= ?
   )
ORDER BY measurement_time;

6-2. データベースからレコードを取得する処理

• スクリプトの引数
  (1) データベースファイルパス
  (2) 気象センサー名
  (3) 検索開始時間
  (4) 検索終了時間(含む)

import argparse
import logging
import os
from typing import Optional, Tuple

import sqlite3
from sqlite3 import Error

# ログフォーマット
LOG_FMT: str = '%(levelname)s %(message)s'

# ...すでに定義している定義・関数は割愛...

if __name__ == '__main__':
    logging.basicConfig(format=LOG_FMT)
    app_logger = logging.getLogger(__name__)
    app_logger.setLevel(level=logging.DEBUG)

    parser: argparse.ArgumentParser = argparse.ArgumentParser()
    # 測定時刻がTEXT型SQLite3 データベースパス
    #  for UnitTime:~/db/weather_db
    parser.add_argument("--sqlite3-db", type=str, required=True,
                        help="測定時刻が INTEGER型の SQLite3 データベースパス")
    # デバイス名: esp8266_1
    parser.add_argument("--device-name", type=str, required=True,
                        help="device name in t_device.")
    # 検索開始時刻
    parser.add_argument("--from-datetime", type=str, required=True,
                        help="(example) 2023-10-30 10:00:00")
    # 検索終了時刻
    parser.add_argument("--to-datetime", type=str, required=True,
                        help="(example) 2023-10-30 12:00:00")
    args: argparse.Namespace = parser.parse_args()
    # データベースパス
    db_path: str = os.path.expanduser(args.sqlite3_db)
    if not os.path.exists(db_path):
        app_logger.warning("database not found!")
        exit(1)

• カーソルから取得したレコードを出力する処理部分

    conn: sqlite3.Connection = Optional[None]
    try:
        conn = get_connection(db_path, logger=app_logger)
        cursor: sqlite3.Cursor = conn.cursor()
        try:
            sql_param: Tuple = (
                args.device_name, args.from_datetime, args.to_datetime
            )
            app_logger.info(sql_param)
            cursor.execute(QUERY_RANGE_DATA, sql_param)
            # 測定時刻(文字列),外気温,室内気温,室内湿度,気圧
            for rec in cursor:
                app_logger.info(f'"{rec[0]}",{rec[1]},{rec[2]},{rec[3]},{rec[4]}')
        finally:
            cursor.close()
    except Exception as err:
        app_logger.warning(err)
        exit(1)
    finally:
        if conn is not None:
            conn.close()

6-2. スクリプトの実行とログ出力

• 測定時刻がINTGER型のデータベースの場合

(py_sqlite3) $ python ReadWeather_unixepoch.py \
> --sqlite3-db ~/db/weather_db --device-name esp8266_1 \
> --from-datetime '2023-10-30 17:50:00' --to-datetime '2023-10-30 18:10:00'
INFO ('esp8266_1', '2023-10-30 17:50:00', '2023-10-30 18:10:00')
INFO "2023-10-30 17:50:44",11.6,19.9,51.4,1017.5
INFO "2023-10-30 18:00:28",11.5,19.9,51.3,1017.6

• 測定時刻がTEXT型のデータベースの場合

(py_sqlite3) $ python ReadWeather_texttime.py \
> --sqlite3-db ~/db/weather_texttime_db --device-name esp8266_1 \
> --from-datetime '2023-10-31 15:30:00' --to-datetime '2023-10-31 16:30:00'
INFO ('esp8266_1', '2023-10-31 15:30:00', '2023-10-31 16:30:00')
INFO "2023-10-31 15:35:29.139",15.7,20.4,49.8,1015.4
INFO "2023-10-31 15:45:13.162",15.5,20.3,50.0,1015.5
INFO "2023-10-31 15:54:57.116",15.0,20.2,49.8,1015.6
INFO "2023-10-31 16:04:41.117",14.8,20.1,49.7,1015.5
INFO "2023-10-31 16:14:26.132",14.5,20.0,49.4,1015.6
INFO "2023-10-31 16:24:08.556",14.3,19.9,49.1,1015.5

7. 結論

　時間データの生成処理にそれほど差異はありませんが、INTEGER型で保持した場合に選択クエリーがSQLite3でしか使用できないのとSQLite関数の使い方が特殊なのが大きな欠点です。個人的には１日あたりのレコード数が少ないのであればTEXT型のほう良いと思います。

　ラズパイでは１センサーあたり10分間隔でデータを登録する必要があるため時間列のサイズの肥大化をさけるため INTEGER型 にしました。※１ヶ月で約4600レコード

• 記事で紹介したコード片のソースコード名

# 1 UDPパケットモニタースクリプト
UdpMonitorFromWeatherSensor.py # 測定時間をINTEGER型で登録
UdpMonitorFromWeatherSensor_textTime.py # 測定時間をTEXT型で登録
# 2 登録データ検索スクリプト
ReadWeather_unixepoch.py # 測定時間がINTEGER型のデータベース
ReadWeatherL_texttime.py # 測定時間がTEXT型のデータベース

ソースコードとSQLite3データベースファイル(サンプル用)、SQLファイル、実行ログは下記GitHubリポジトリで公開しています。

GitHub(pipito-yukio) qiita-posts/python/sqlite_timestamp