SQLServer 上の datetime の表記形式について

2014年12月6日2020年9月2日

CentOS 上の php から SQL Server にアクセスしたときのこと。 datetime 型のデータを取得するために php で以下のように組んでみました。 （エラー処理はここでは省略しています）

#!/usr/bin/php


そしてこれを実行すると、ウチの環境では
Nov 18 2014 09:15:26:887AM

が表示されます。

これを例えば、
2014-11-18 09:15:26

という表記で取得するにはどうしたら良いだろう？　

と、調べた結果を記録しておきます。

tsql で SQL Server にアクセス
php で実行する前に、tsql での動作を確認＆対策を考えてみます。
freetds に付いてくる tsql では、
  tsql <--> freetds library (libsybdb.so) <--> SQL Server

の流れで SQL Server とのやりとりをしているみたいです。たぶん。

SQL を変更してみる
まずは tsql で何のヒネリも無く datetime 型を取得してみます。
$ tsql -Shost -Uuser
Password: ********
locale is "ja_JP.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select datetime_field from table where ...
2> GO
datetime_field
11月 18 2014 09:15:26:887
(1 row affected)

この日時表記は望みのものではないので、select 文をちょっと変更してみます。
1> select convert(varchar,datetime_field,120) from table where ...
2> GO

2014-11-18 09:15:26

これでうまくゆきました。ちなみに、convert() 中の 120 という数値が日時表記のスタイル指定になります。
詳しくは ここ を参照願います。

ただ、select * from ... で取得する場合には、この方法は使えないようです。

locales.conf を変更してみる１
上記とは別に、locales.conf というファイルを修正しても日時表記を変更することができます。

CentOS では、freetds を yum でインストールした場合は /etc に、
ソースからビルド＆インストールした場合は /usr/local/etc に在って、その中身は
[default]
    date format = %b %e %Y %I:%M:%S:%z%p

[en_US]
    date format = %b %e %Y %I:%M:%S:%z%p
    language = us_english
    charset = iso_1

[es_ES]
    date format = %b %d %Y %I:%M%p
    language = spanish
    charset = iso_1

[pt_BR]
    date format = %d/%m/%Y %H:%M
    language = Portuguese
    charset = iso_1

[it_IT]
    date format = %d/%m/%Y %H:%M
    language = Italiano
    charset = iso_1
このようになっています。

FreeTDS公式サイトのユーザーガイドの ここ によると、
[default]
    date format = %b %e %Y %I:%M:%S:%z%p
[default] セクション内の date format を変更してみたら良いようです。そこで、
[default]
#   date format = %b %e %Y %I:%M:%S:%z%p
    date format = %Y-%m-%d %H:%M:%S
と変更してから以下のように実行すると、
1> select datetime_field from table where ...
2> GO
datetime_field
2014-11-18 09:15:26
(1 row affected)

SQL 文を変更せずに、望みどおりの日時形式で返ってきました。

locales.conf を変更してみる２
さきほどの locales.conf をよく見ると、[ja_JP] というセクションを作って
その中で date format を指定してやってもよさそうです。そこで、
[default]
    date format = %b %e %Y %I:%M:%S:%z%p
#   date format = %Y-%m-%d %H:%M:%S

[default] の date format を元に戻して、

[ja_JP]
    date format = %Y-%m-%d %H:%M:%S

の２行を追記して SQL 文を実行してみました。

1> select datetime_field from table where ...
2> GO
datetime_field
2014-11-18 09:15:26
(1 row affected)

この方法でも問題ないようです。tsql を使う限りは・・・

php で SQL Server にアクセス

では本題の php による SQL Server へのアクセスです。php では、

  php <--> mssql.so <--> freetds library (libsybdb.so) <--> SQL Server

の流れで SQL Server とのやりとりをしているようです。

SQL を変更してみる

まず、上述で追記した [ja_JP] の２行を削除しておきます。 そして、冒頭の sample.php の４行めの SQL 文を次のように変更します。

    $result = mssql_query( "select convert(varchar,datetime_field,120) from table where ..." );

sample.php を実行してみると、

$ ./sample.php
2014-11-18 09:15:26

tsql で実行したときと同様に表示されました。

locales.conf の [default] を変更

まず、sample.php の４行めを元に戻しておきます。

    $result = mssql_query( "select datetime_field from table where ..." );

そして locales.conf の [default] を以下のように変更します。

[default]
#   date format = %b %e %Y %I:%M:%S:%z%p
    date format = %Y-%m-%d %H:%M:%S

これで sample.php を実行します。

$ ./sample.php
2014-11-18 09:15:26

この方法でも tsql と同様に表示され、問題なしです。

locales.conf に [ja_JP] を追記

では locales.conf で [ja_JP] の追記については、実験するまでもないでしょうか？ 実はこれで結構ハマってしまいました。 locales.conf を以下のように、[default] を元に戻して、末尾に [ja_JP] セクションを追記します。

[default]
    date format = %b %e %Y %I:%M:%S:%z%p
#   date format = %Y-%m-%d %H:%M:%S

[ja_JP]
    date format = %Y-%m-%d %H:%M:%S

これで sample.php を実行します。

$ ./sample.php
Nov 18 2014 09:15:26:887AM

！？　表記が変わってくれません・・・ （ウチの環境ではダメでしたが、他の環境ではうまくこともあるかもしれません） php においてのこの方法は、保留にしておきます。

php.ini の mssql.datetimeconvert を変更

ところで php.ini には、以下の記述があります。(1480行め付近)

; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mm:ss
;mssql.datetimeconvert = On

mssql.datetimeconvert の設定によって datetime 型の表記が変えられるようです。 無指定の場合は On になりますので、表記は SQL Server の設定によりけり、となるようです。 これを Off にすると datetime 型は YYYY-MM-DD hh:mm:ss 形式で返す、と ハッキリ書いてあるので、さっそくこれを Off にしてみましょう。

;mssql.datetimeconvert = On
mssql.datetimeconvert = Off

先ほど locales.conf に追記した [ja-JP] の２行は削除して、sample.php を実行してみると、

$ ./sample.php
2014-11-18 09:15:26

php.ini のコメントどおりの表記になりました！

まとめ

php から SQL Server の datetime 型にアクセスする場合に、 日時表記を変更するためには、以下のいずれかでいけるようです。

・SQL 文中に convert(varchar,fieldname,style) を使用して、指定したstyleでdatetime型を文字列に変換する。
注意：select * from … を利用する場合には convert() を使用できない
・locales.conf の [default] セクションの date format の値を変更する。
注意：tsql, php からの SQL Server へのアクセスで、datetime 型の表記が統一される。
・php.ini の mssql.datetimeconvert = Off を指定する。
注意：php からの SQL Server へのアクセスで、datetime 型の表記が統一される。

ケースバイケースで使って分けてゆきましょう。 なお、php からの SQL Server へのアクセスにおいて locales.conf の [ja_JP] の追記が反映されないのは、 調べてみた結果、freetds 側の locales() の戻り値の処理の仕方によるもの、と思われます。 （locales() の戻り値は、ja_JP などの５文字とは限らないようです）