UNIXタイムスタンプからその日の23:59:59や0:00:00を取得するSQL
日時を比較してデータを取得したい時など、何時何分何秒！という厳密なものでの比較ではなく
それぞれの日の0:00:00丁度とか、23:59:59とかを使いたい時ってあると思います。
で、値を見てみたらUNIXタイムスタンプ？うわぁめんどくさそう…とか思ってしまう自分のためにSQLをメモ。
- MySQL
- table_nameテーブルのdateに下記のようなタイムスタンプが入っている場合
sample
|
1 2 3 4 |
SELECT date FROM table_name LIMIT 3; |
1395988136
1396074906
1396331516
タイムスタンプを文字列形式にする とこんな感じですが
|
1 2 3 4 |
SELECT FROM_UNIXTIME(date) FROM table_name LIMIT 3; |
2014-03-28 15:28:56
2014-03-29 15:35:06
2014-04-01 14:51:56
タイムスタンプから、それぞれの日の23:59:59を取得
|
1 2 3 4 |
SELECT ADDDATE(FROM_UNIXTIME(date, "%Y-%m-%d"), INTERVAL 86399 SECOND) FROM table_name LIMIT 3; |
2014-03-28 23:59:59
2014-03-29 23:59:59
2014-04-01 23:59:59
タイムスタンプから、それぞれの日の0:00:00を取得
|
1 2 3 4 |
SELECT ADDDATE(FROM_UNIXTIME(date, "%Y-%m-%d"), INTERVAL 0 SECOND) FROM table_name LIMIT 3; |
2014-03-28 00:00:00
2014-03-29 00:00:00
2014-04-01 00:00:00
0:00:00での比較の場合、 タイムスタンプを年月日のみの文字列形式にフォーマット
でも良いのでは？とも思ったりしますが
|
1 2 3 4 |
SELECT FROM_UNIXTIME(date, "%Y-%m-%d") FROM table_name LIMIT 3; |
2014-03-28
2014-03-29
2014-04-01
まぁ、場合によるんでしょうか。
おまけ： FROM_UNIXTIME()について
そういえば、UNIXタイムスタンプを文字列形式にした際の結果ですが
2014-03-28 15:28:56
あれ？日本標準時（JST）だ。
UNIXタイムスタンプは協定世界時（UTC）をベースにしているはずなのに何故…？
と思ったので、以下、メモしておきます。
MySQL の FROM_UNIXTIME() は、
現在のタイムゾーンを反映した値を返してくる…！！
ちょっと怖いですね(´・ω・`)
返ってきた値がUTCだと思い込まないように注意しよう…