TSQLでdatetimeoffsetを文字列変換するよ！

※注意※　SQL Server 2016以降のはなしです。

サーバーっていうものに「時間くだしゃい」っておねだりしたとき、くれる値は結構まちまちだったりする。特にクラウドの、IaaSだのPaaSだののサーバー。

それは何でかって、ロケールが違うからです。つまり、JSTとかUTCとかのアレで時間がアレする。だからDateTimeじゃなくて、ロケールまで考慮したDateTimeOffsetを使わなければならないよね。じゃないと整合性とるのがクソ辛くなるよね。

そいで、例えば「昨日のデータ」やら「明日のデータ」ってものを扱おうとしたとき、日付を何も考えず「yyyy/MM/dd」でFORMATすると無様に殺されてドブに捨てられる羽目になる。

SQL Serverも多分に漏れず慎重な時間の扱いが求められるわけであるが、そんな時にお役立ちなのが「AT TIME ZONE」さんです。

参考：AT TIME ZONE (Transact-SQL)

◆説明

説明つらいから以下のクエリを実際に動かして確認してくりゃれ。

DECLARE @d1 datetime = '2019/04/30 23:00:00';
DECLARE @d2 datetimeoffset = '2019/04/30 23:00:00 +00:00';
DECLARE @d3 datetimeoffset = '2019/04/30 01:00:00 +09:00';

SELECT
 @d1 AS d1,
 @d2 AS d2,
 @d3 AS d3,
 FORMAT(@d1 AT TIME ZONE 'Tokyo Standard Time', 'yyyy/MM/dd') AS d1_jst,
 FORMAT(@d2 AT TIME ZONE 'Tokyo Standard Time', 'yyyy/MM/dd') AS d2_jst,
 FORMAT(@d2 AT TIME ZONE 'UTC', 'yyyy/MM/dd') AS d2_utc,
 FORMAT(@d3 AT TIME ZONE 'Tokyo Standard Time', 'yyyy/MM/dd') AS d3_jst,
 FORMAT(@d3 AT TIME ZONE 'UTC', 'yyyy/MM/dd') AS d3_utc

AT TIME ZONEの何が凄いって、つまりUTCのdatetimeoffsetがぶち込まれたときに「オフセット付きJST時間」に変換してくれることだ。

注目はd2_jst列ですね。これは日付をまたいで5/1になってくれる。エライ。逆にd3_utc列は4/29になってくれる。エロい。

◆おまけ：C#でJST時間つくるやつ

▼現在時刻

var jstTimeZone = System.TimeZoneInfo.FindSystemTimeZoneById("Tokyo Standard Time");
DateTimeOffset jst = System.TimeZoneInfo.ConvertTime(DateTimeOffset.UtcNow, jstTimeZone)
Console.WriteLine(jst);

▼YYYYMMDD文字列から

var jstYYYYMMDD = "20190430";
var jstOffset = TimeZoneInfo.FindSystemTimeZoneById("Tokyo Standard Time").BaseUtcOffset;
var offsetString = (jstOffset < TimeSpan.Zero ? "-" : "+") + jstOffset.ToString(@"hh':'mm");
var jst = DateTimeOffset.ParseExact($"{jstYYYYMMDD} {offsetString}", "yyyyMMdd zzz", null);
Console.WriteLine(jst);

▼yyyyMMddHHmmss文字列から

var jstYYYYMMDD = "20190430130000";
var jstOffset = TimeZoneInfo.FindSystemTimeZoneById("Tokyo Standard Time").BaseUtcOffset;
var offsetString = (jstOffset < TimeSpan.Zero ? "-" : "+") + jstOffset.ToString(@"hh':'mm");
var jst = DateTimeOffset.ParseExact($"{jstYYYYMMDD} {offsetString}", "yyyyMMddHHmmss zzz", null);
Console.WriteLine(jst);

◆結論

ジーンズ洗うと雑巾みたいな匂いするんだけどどうすればいいんだろう。