Microsoft.Azure.Cosmos.Table.TableQuery でローカル時間の検索クエリ生成

horihori

2023年11月23日 23:18

背景

とあるログ管理サービスを開発中
- 開発環境：visual studio 2022
- Azure Functions
- .Net 6.0
ログ管理テーブルには次のデータが格納されている
- 顧客No. (PartitionKey, String)
- ログアップロード時刻 (RowKey, String)
- 接続開始時間 (DateTime)
- IPアドレス (String)
- etc.
クライアントから渡されたパラメータを取得し、クエリに基づくデータを返すAPIを開発する

やりたいこと

クライアント側で指定した日付フィルタを取得し、接続開始時間による絞り込み検索
- フィルタの開始日、終了日はGetパラメータで与えられる
  （ここに載せるプログラムはこのプロセスは省略）

困りごと

TableQueryを作ろうとすると、日付がUTC変換されてしまう…

// 検索日
DateTime startDateTime = DateTime.Parse("20231010");
  // ここでは startDateTime = '2023-10-10T00:00:00.0000000Z'
DateTime endDateTime = DateTime.Parse("20231015").AddDays(1);
  // ここでは endDateTime = '2023-10-16T00:00:00.0000000Z'

// 検索開始日より日付が後のものを取得するフィルタ
string startFilter = TableQuery.GenerateFilterConditionForDate("ConnectionStartTime", QueryComparisons.GreaterThanOrEqual, startDateTime);
  // これが startFilter = 'ConnectionStartTime ge datetime'2023-10-09T15:00:00.0000000Z' となってしまう

// 検索終了日より日付が前のものを取得するフィルタ
string endFilter = TableQuery.GenerateFilterConditionForDate("ConnectionStartTime", QueryComparisons.LessThan, endDateTime);
  // これが endFilter = 'ConnectionStartTime lt datetime'2023-10-14T15:00:00.0000000Z' となってしまう

// フィルタを結合
string combinedFilter = TableQuery.CombineFilters(startFilter, TableOperators.And, endFilter);

// TableQuery生成
TableQuery<Entity> query = new TableQuery<Entity>().Where(combinedFilter);
  // ここのQueryが((ConnectionStartTime ge datetime'2023-10-09T15:00:00.0000000Z') and (ConnectionStartTime lt datetime'2023-10-15T15:00:00.0000000Z'))

例えば、2023/10/10 0:00:00 ~ 2023/10/15 23:59:59… で検索したいのに、
2023/10/9 20:00:00 のデータを取得してしまったり、
2023/10/15 22:00:00 のデータが取得できない問題が発生します。
（接続開始時間がUTCで保存されていたら問題ないのですが…）

解決方法

何度かChatGPTと議論を繰り返し、ようやくたどり着いたのがTableQuery.GenerateFilterConditionForDate を使わず文字列で直指定する方法。

// 検索日
string startDate = "20231010";
string endDate = "20231015";

// タイムゾーン情報を取得（例：日本標準時）
TimeZoneInfo jstZone = TimeZoneInfo.FindSystemTimeZoneById("Tokyo Standard Time");
// DateTimeOffset に変換
DateTimeOffset startDateTime = TimeZoneInfo.ConvertTime(DateTime.ParseExact(startDate, "yyyyMMdd", null), jstZone);
DateTimeOffset endDateTime = TimeZoneInfo.ConvertTime(DateTime.ParseExact(endDate, "yyyyMMdd", null).AddDays(1), jstZone);

// 検索開始日より日付が後のものを取得するフィルタ
string startFilter = $"(ConnectionStartTime ge datetime'{startDateTime:yyyy-MM-ddTHH:mm:ss.fffZ}')";

// 検索終了日より日付が前のものを取得するフィルタ
string endFilter = $"(ConnectionStartTime lt datetime'{endDateTime:yyyy-MM-ddTHH:mm:ss.fffZ}')";

// フィルタを結合
string combinedFilter = TableQuery.CombineFilters(startFilter, TableOperators.And, endFilter);

// TableQuery生成
TableQuery<DynamicTableEntity> query = new TableQuery<DynamicTableEntity>().Where(combinedFilter);

DateTimeは世界標準時で検索せよと言うMicrosoft & OpenAI v.s. 日本時間で登録されてるデータは日本時間で検索してよと思う私の対決は、５時間の激闘の末、私が勝利を収めました。

参考までに

DateTimeOffsetで時差の情報も持たせたのに、TableQueryで無効化されるのは納得いかない…
↓ChatGPTの間違った回答

// タイムゾーン情報を取得（例：日本標準時）
TimeZoneInfo jstZone = TimeZoneInfo.FindSystemTimeZoneById("Tokyo Standard Time");

// クエリで与えられた文字列をDateTimeに変換
string startDate = "20231010";
string endDate = "20231015";
DateTimeOffset startDateTime = TimeZoneInfo.ConvertTime(DateTime.ParseExact(startDate, "yyyyMMdd", null), jstZone);
DateTimeOffset endDateTime = TimeZoneInfo.ConvertTime(DateTime.ParseExact(endDate, "yyyyMMdd", null).AddDays(1), jstZone);

// 開始日 < ログ < 終了日
var queryB = TableQuery.CombineFilters(
    TableQuery.GenerateFilterConditionForDate("ConnectionStartTime", QueryComparisons.GreaterThan, startDateTime.UtcDateTime),
    TableOperators.And,
    TableQuery.GenerateFilterConditionForDate("ConnectionStartTime", QueryComparisons.LessThan, endDateTime.UtcDateTime));

// このクエリが(ConnectionStartTime gt datetime'2023-10-10T00:00:00.0000000Z') and (ConnectionStartTime lt datetime'2023-10-16T00:00:00.0000000Z')のようにローカル時間で変換される

いいえ、これでは残念ながら、クエリは(ConnectionStartTime gt datetime'2023-10-09T15:00:00.0000000Z') and (ConnectionStartTime lt datetime'2023-10-15T15:00:00.0000000Z')となってしまいます。

編集後記

結局、同PJメンバーがなんか勝手に接続開始時間をUTCで保存することに決めたらしく、このコードが使われることは無くなりました…TT