日付型が条件にあるSQLをライブラリを介して発行した際に困ったことになった

とある言語のライブラリをロクに検証せず使っていたら困ったことになって、そこ気にしないといけないのだっけと思った次第です。無知なだけです。
PostgreSQLを使っています。DATE 型のカラムに対してライブラリを介して日付型のパラメータを渡した際の挙動どうなるかを確認して行きたいと思います。

準備
PostgreSQLで確認
ライブラリを経由してみる
まとめ

準備

postgres=# SELECT version();
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

CREATE TABLE sample
(
  id serial,
  name text,
  start_date date NOT NULL,
  end_date date NOT NULL,
  start_datetime timestamp without time zone NOT NULL,
  end_datetime timestamp without time zone NOT NULL,
  PRIMARY KEY (id)
);

-- データ登録
INSERT INTO sample (name, start_date, end_date, start_datetime, end_datetime)
VALUES ('Alice', CURRENT_DATE, CURRENT_DATE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

※ CURRENT_DATEとCURRENT_TIMESTAMPを利用してデータを登録していますが、途中外出して日付変わってしまったので入れなおしてます。

PostgreSQLで確認

いったん素のSQLで念のため確認します。

CURRENT_DATEで確認（DATE = DATE）

-- CURRENT_DATEとCURRENT_TIMESTAMPを型を変換するパターンで確認
postgres=# SELECT * FROM sample WHERE CURRENT_DATE BETWEEN start_date AND end_date;
 id | name  | start_date |  end_date  |       start_datetime       |        end_datetime        
----+-------+------------+------------+----------------------------+----------------------------
  1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743 
(1 row)

これはDATEとDATEで時分秒は関係ないので1件取得できました。期待通り。

CURRENT_DATE::TIMESTAMPで確認（TIMESTAMP = DATE）

postgres=# SELECT * FROM sample WHERE CURRENT_DATE::TIMESTAMP BETWEEN start_date AND end_date;
 id | name  | start_date |  end_date  |       start_datetime       |        end_datetime        
----+-------+------------+------------+----------------------------+---------------------------- 
  1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743  
(1 row)

これはTIMESTAMPとDATEですが、'2022-05-28 00:00:00'になると思っているので1件取得できました。期待通り。

postgres=# EXPLAIN SELECT * FROM sample WHERE CURRENT_DATE::TIMESTAMP BETWEEN start_date AND end_date;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Seq Scan on sample  (cost=0.00..33.00 rows=102 width=60)
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Filter: (((CURRENT_DATE)::timestamp without time 
zone >= start_date) AND ((CURRENT_DATE)::timestamp without time 
zone <= end_date))

postgres=# SELECT (CURRENT_DATE)::timestamp;
-[ RECORD 1 ]+--------------------
current_date | 2022-05-28 00:00:00

CURRENT_TIMESTAMPで確認（TIMESTAMP = DATE）

postgres=# SELECT * FROM sample WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date;
 id | name | start_date | end_date | start_datetime | end_datetime
----+------+------------+----------+----------------+--------------
(0 rows)

これはTIMESTAMPとDATEで、'2022-05-28 10:59:26'などになるので取得できません。期待通り。

CURRENT_TIMESTAMP::DATEで確認（DATE = DATE）

postgres=# SELECT * FROM sample WHERE CURRENT_TIMESTAMP::DATE BETWEEN start_date AND end_date;
 id | name  | start_date |  end_date  |       start_datetime       |        end_datetime        
----+-------+------------+------------+----------------------------+---------------------------- 
  1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743  
(1 row)

これはDATEとDATEで、'2022-05-28 10:59:26'::DATEなどが'2022-05-28'::DATEで取得できました。期待通り。

postgres=# SELECT (CURRENT_TIMESTAMP)::date;
-[ RECORD 1 ]-----+-----------
current_timestamp | 2022-05-28

日時部分を文字列でDATEに型変換で確認（DATE = DATE）

postgres=# SELECT * FROM sample WHERE '2022-05-28 10:59:26'::DATE BETWEEN start_date AND end_date;
 id | name  | start_date |  end_date  |       start_datetime    
   |        end_datetime
----+-------+------------+------------+----------------------------+----------------------------
  1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743
(1 row)

これはDATEとDATEで時分秒は関係ないので1件取得できました。期待通り。

日時部分を文字列でTIMESTAMPに型変換で確認（TIMESTAMP = DATE）

postgres=# SELECT * FROM sample WHERE '2022-05-28 10:59:26'::TIMESTAMP BETWEEN start_date AND end_date;~
 id | name | start_date | end_date | start_datetime | end_datetime
----+------+------------+----------+----------------+--------------
(0 rows)

これはTIMESTAMPとDATEで、'2022-05-28 10:59:26'などになるので取得できません。期待通り。

日時部分を文字列で明示的な型変換なしで確認（? = DATE）

postgres=# SELECT * FROM sample WHERE '2022-05-28 10:59:26' BETWEEN start_date AND end_date;
 id | name  | start_date |  end_date  |       start_datetime    
   |        end_datetime
----+-------+------------+------------+----------------------------+----------------------------
  1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743
(1 row)

これは'2022-05-28 10:59:26'が暗黙の型変換でDATEになり取れているということですかね。

postgres=# EXPLAIN SELECT * FROM sample WHERE '2022-05-28 10:59:26' BETWEEN start_date AND end_date;
-[ RECORD 1 ]-----------------------------------------------------------------------------------
QUERY PLAN | Seq Scan on sample  (cost=0.00..23.80 rows=102 width=60)
-[ RECORD 2 ]-----------------------------------------------------------------------------------
QUERY PLAN |   Filter: (('2022-05-28'::date >= start_date) AND ('2022-05-28'::date <= end_date))

EXPLAINで見ると'2022-05-28'::dateとキャストされているようです。

ライブラリを経由してみる

C#のものは今回の話の流れとして必要でしたが、他は本当は色々試したかったけど、という感じです。

`C#`

EF Core(.NET6)

概要 - EF Core | Microsoft Docs

$ dotnet --version
6.0.300

# 新しいプロジェクトを作成する
$ dotnet new console -o EFGetStarted

リバースエンジニアリング - EF Core | Microsoft Docs

$ dotnet tool install --global dotnet-ef
$ dotnet add package Microsoft.EntityFrameworkCore.Design

# モデルを作成する
$ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
$ dotnet ef dbcontext scaffold "Host=localhost;Port=15432;Database=postgres;Username=postgres;Password=password" Npgsql.EntityFrameworkCore.PostgreSQL

namespace EFGetStarted
{
    public partial class Sample
    {
        public int Id { get; set; }
        public string? Name { get; set; }
        public DateOnly StartDate { get; set; }
        public DateOnly EndDate { get; set; }
        public DateTime StartDatetime { get; set; }
        public DateTime EndDatetime { get; set; }
    }
}

ちなみにDateOnlyは.Net6からです。
※ usingは省略しています。

namespace EFGetStarted;

internal class Program
{
  private static void Main()
  {
    using (var db = new postgresContext())
    {
      var now = DateOnly.FromDateTime(DateTime.Now);
      Console.WriteLine(now);

      var sample = db.Samples
          .Where(s => s.StartDate >= now && s.EndDate <= now)
          .First();
      Console.WriteLine($"{sample.Id} {sample.Name} {sample.StartDate} {sample.EndDate} {sample.StartDatetime} {sample.EndDatetime}");
    }
  }
}

DateOnly.FromDateTime(DateTime.Now)を使って範囲検索してみます。

$ dotnet run
2022/05/28
1 Alice 2022/05/28 2022/05/28 2022/05/28 10:54:26 2022/05/28 10:54:26

DateOnly型を明示的に使う側で渡しているのもあり、期待した形で取得できました。

EF Core(.NET Core 3.1)

使用する .NET のバージョンを選択する - .NET | Microsoft Docs

$ dotnet new globaljson --sdk-version 3.1.419

$ dotnet --version
3.1.419

# 新しいプロジェクトを作成する
$ dotnet new console -o ../EFGetStarted3.1

$ dotnet new tool-manifest
$ dotnet tool install dotnet-ef --version 3.1.25
$ dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.25

# モデルを作成する
$ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 3.1.18
$ dotnet ef dbcontext scaffold "Host=localhost;Port=15432;Database=postgres;Username=postgres;Password=password" Npgsql.EntityFrameworkCore.PostgreSQL

namespace EFGetStarted3._1
{
    public partial class Sample
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
        public DateTime StartDatetime { get; set; }
        public DateTime EndDatetime { get; set; }
    }
}

DateOnlyは.Net6からなのでDateTimeになりました。

namespace EFGetStarted3._1
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.WriteLine("Now");
      var now = DateTime.Now;
      Test(now);

      Console.WriteLine("Today");
      var today = DateTime.Today;
      Test(today);
    }

    static void Test(DateTime date)
    {
      Console.WriteLine(date);

      using (var db = new postgresContext())
      {
        var sample = db.Sample
            .Where(s => s.StartDate >= date && s.EndDate <= date)
            .First();
        Console.WriteLine($"{sample.Id} {sample.Name} {sample.StartDate} {sample.EndDate} {sample.StartDatetime} {sample.EndDatetime}");
      }
    }
  }
}

DateTime.NowとDateTime.Todayを使って範囲検索してみます。

$ dotnet run
Now
2022/05/28 17:05:40
1 Alice 2022/05/28 0:00:00 2022/05/28 0:00:00 2022/05/28 10:54:26 2022/05/28 10:54:26
Today
2022/05/28 0:00:00
1 Alice 2022/05/28 0:00:00 2022/05/28 0:00:00 2022/05/28 10:54:26 2022/05/28 10:54:26

両方とも検索できました。

2022-05-29 15:06:47.599 JST [107] LOG:  execute <unnamed>: SELECT s.id, s.end_date, s.end_datetime, s.name, s.start_date, s.start_datetime
    FROM sample AS s
    WHERE (s.start_date >= $1) AND (s.end_date <= $1)
    LIMIT 1
2022-05-29 15:06:47.599 JST [107] DETAIL:  parameters: $1 = '2022-05-29'
2022-05-29 15:06:47.599 JST [107] LOG:  duration: 0.014 ms  plan:
    Query Text: SELECT s.id, s.end_date, s.end_datetime, s.name, s.start_date, s.start_datetime
    FROM sample AS s
    WHERE (s.start_date >= $1) AND (s.end_date <= $1)
    LIMIT 1
    Limit  (cost=0.00..0.23 rows=1 width=60)
      ->  Seq Scan on sample s  (cost=0.00..23.80 rows=102 width=60)
            Filter: ((start_date >= '2022-05-29'::date) AND (end_date <= '2022-05-29'::date))

PostgresのログをみるとパラメータがNowの時でも'2022-05-29'となってました。

Dapper

$ dotnet --version
6.0.300

# 新しいプロジェクトを作成する
$ dotnet new console -o ../DapperGetStarted

$ dotnet add package Dapper --version 2.0.123
$ dotnet add package Npgsql --version 6.0.4

エンティティはEF Coreと同じものを使用しています。

namespace DapperGetStarted
{
  class Program
  {
    static async Task Main(string[] args)
    {
      Console.WriteLine("Now");
      var now = DateTime.Now;
      await TestAsync(now);

      Console.WriteLine("Today");
      var today = DateTime.Today;
      await TestAsync(today);
    }

    static async Task TestAsync(DateTime date)
    {
      Console.WriteLine(date);

      using (var connection =
          new NpgsqlConnection("Host=localhost;Port=15432;Database=postgres;Username=postgres;Password=password"))
      {
        var sample = (await connection.QueryAsync<Sample>(
            "SELECT * FROM sample WHERE start_date >= @start_date AND end_date <= @end_date",
            new { start_date = date, end_date = date })).FirstOrDefault();
        if (sample != null)
        {
          Console.WriteLine($"{sample.Id} {sample.Name} {sample.StartDate} {sample.EndDate} {sample.StartDatetime} {sample.EndDatetime}");
        }
      }
    }
  }
}

同じくDateTime.NowとDateTime.Todayを使って範囲検索してみます。

$ dotnet run
Now
2022/05/28 23:01:55
Today
2022/05/28 0:00:00
1 Alice 0001/01/01 0:00:00 0001/01/01 0:00:00 0001/01/01 0:00:00 0001/01/01 0:00:00

DateTime.Nowは取得できません。型情報はDateTime以外わからないので、こうことになってしまう感じですかね。

# DateTime.Now
2022-05-29 15:12:20.015 JST [114] LOG:  execute <unnamed>: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2
2022-05-29 15:12:20.015 JST [114] DETAIL:  parameters: $1 = '2022-05-29 15:12:19.474152', $2 = '2022-05-29 15:12:19.474152'
2022-05-29 15:12:20.019 JST [114] LOG:  duration: 3.971 ms  plan:
    Query Text: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2
    Seq Scan on sample  (cost=0.00..23.80 rows=102 width=60)
      Filter: ((start_date >= '2022-05-29 15:12:19.474152'::timestamp without time zone) AND (end_date <= '2022-05-29 15:12:19.474152'::timestamp without time zone))

# DateTime.Today
2022-05-29 16:30:03.040 JST [210] LOG:  execute <unnamed>: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2
2022-05-29 16:30:03.040 JST [210] DETAIL:  parameters: $1 = '2022-05-29 00:00:00', $2 = '2022-05-29 00:00:00'
2022-05-29 16:30:03.040 JST [210] LOG:  duration: 0.009 ms  plan:
    Query Text: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2
    Seq Scan on sample  (cost=0.00..23.80 rows=102 width=60)
      Filter: ((start_date >= '2022-05-29 00:00:00'::timestamp without time zone) AND (end_date <= '2022-05-29 00:00:00'::timestamp without time zone))

パラメータは「timestamp」でキャストされている。
また、DateOnlyは「Unhandled exception. System.NotSupportedException: The member start_date of type System.DateOnly cannot be used as a parameter value」でまだ使えないみたいです。

Rails(Ruby)

ActiveRecord

$ rails db:schema:dump
$ rails generate model Sample --skip-migration

テーブル名sampleにしてしまっていたって気づきました。

$ rails c
irb(main):001:0> now = Time.now
=> 2022-05-29 15:17:06.6508589 +0900
irb(main):002:0> Sample.where("start_date >= ?", now).where("end_date <= ?", now)   
  Sample Load (2.9ms)  SELECT "sample".* FROM "sample" WHERE (start_date >= '2022-05-29 06:17:06.650858') AND (end_date <= '2022-05-29 06:17:06.650858')
=> 
[#<Sample:0x00007fea7fcf6608
  id: 1,
  name: "Alice",
  start_date: Sun, 29 May 2022,
  end_date: Sun, 29 May 2022,
  start_datetime: Sun, 29 May 2022 14:04:26.409945000 UTC +00:00,
  end_datetime: Sun, 29 May 2022 14:04:26.409945000 UTC +00:00>]

WHERE (start_date >= '2022-05-29 06:17:06.650858')ってなってるけど、取得できている。

2022-05-29 15:17:13.364 JST [120] LOG:  execute <unnamed>: SELECT "sample".* FROM "sample" WHERE (start_date >= '2022-05-29 06:17:06.650858') AND (end_date <= '2022-05-29 06:17:06.650858')
2022-05-29 15:17:13.364 JST [120] LOG:  duration: 0.010 ms  plan:
    Query Text: SELECT "sample".* FROM "sample" WHERE (start_date >= '2022-05-29 06:17:06.650858') AND (end_date <= '2022-05-29 06:17:06.650858')
    Seq Scan on sample  (cost=0.00..23.80 rows=102 width=60)
      Filter: ((start_date >= '2022-05-29'::date) AND (end_date <= '2022-05-29'::date))

Dapperとは異なりパラメータ直接埋まってます。

TypeScript

Prisma

Add Prisma to an existing project | Prisma Docs

# Set up Prisma
$ npm install prisma --save-dev
$ npx prisma init

# 事前に.envのDATABASE_URLを修正
# Introspect your database with Prisma
$ npx prisma db pull

# Install and generate Prisma Client
$ npm install @prisma/client
$ npx prisma generate

# Querying the database
$ npx ts-node index.ts

model sample {
  id             Int      @id @default(autoincrement())
  name           String?
  start_date     DateTime @db.Date
  end_date       DateTime @db.Date
  start_datetime DateTime @db.Timestamp(6)
  end_datetime   DateTime @db.Timestamp(6)
}

スキーマの型がDateTime @db.DateとDateTime @db.Timestamp(6)で認識されています。この時点で良さそうです。

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({
  // https://www.prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/logging
  log: ['query', 'info', 'warn', 'error'],
});

async function test(date: Date): Promise<void> {
  console.log({ date })

  const samples = await prisma.sample.findMany({
    where: { AND: [{ start_date: { lte: date } }, { end_date: { gte: date } }] },
  })
  console.log({ samples })
}

async function main() {
  const now = new Date()
  await test(now)
}

main()
  .catch((e) => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

new Date()を使って範囲検索してみます。

$ npx ts-node index.ts
{ date: 2022-05-29T06:25:09.893Z }
{
  samples: [
    {
      id: 1,
      name: 'Alice',
      start_date: 2022-05-29T00:00:00.000Z,    
      end_date: 2022-05-29T00:00:00.000Z,      
      start_datetime: 2022-05-29T14:04:26.409Z,
      end_datetime: 2022-05-29T14:04:26.409Z   
    }
  ]
}

取得できました。

2022-05-29 15:25:10.321 JST [128] LOG:  execute s0: SELECT "public"."sample"."id", "public"."sample"."name", "public"."sample"."start_date", "public"."sample"."end_date", "public"."sample"."start_datetime", "public"."sample"."end_datetime" FROM "public"."sample" WHERE ("public"."sample"."start_date" <= $1 AND "public"."sample"."end_date" >= $2) OFFSET $3
2022-05-29 15:25:10.321 JST [128] DETAIL:  parameters: $1 = '2022-05-29', $2 = '2022-05-29', $3 = '0'
2022-05-29 15:25:10.321 JST [128] LOG:  duration: 0.010 ms  plan:
    Query Text: SELECT "public"."sample"."id", "public"."sample"."name", "public"."sample"."start_date", "public"."sample"."end_date", "public"."sample"."start_datetime", "public"."sample"."end_datetime" FROM "public"."sample" WHERE ("public"."sample"."start_date" <= $1 AND "public"."sample"."end_date" >= $2) OFFSET $3
    Seq Scan on sample  (cost=0.00..23.80 rows=102 width=60)
      Filter: ((start_date <= '2022-05-29'::date) AND (end_date >= '2022-05-29'::date))

渡っているパラメータが'2022-05-29'となってます。

まとめ

結果はDapperだけ気を付ける必要がありました。Dapperが悪いわけではありません。何種類かのライブラリをチュートリアル的に触る感じになってしまいましたが、結論的にはちゃんと単体テストしようねということですね。

ぼくは明日、昨日のじぶんに頼りたい

明日のためのメモです。