これをマスター
SQLでの時間の扱い方 NOW や DATEADD などをマスターしましょう！
マスター 現在の時間から(動的に)過去2ヶ月コーヒーを買ってくれたお客さんを探す事が出来る
はじめに
SQLでの時間の取り扱いは、文字列や数字に比べて少しトリッキーだったりします
文字や数字は今日でも明日でも10は十ですが
時間だと、今日は今日ですが、明日の今日は今の明日になります
上司に「今日までの売上データ取っといて」と4/1に言われたとします
(簡略化しますが)データをとるのに、WHERE [売上日]=’4/1′ のクエリを作ったとします
後日、上司に「この前のデータいつでも見れるようにして」と言われたら
日付は「いつでも」に自動で合うようにならないとですね
Excelでも似たケースでファイルを開いた時に更新されるようにしますが
SQLだとクエリが動いた時刻をとる NOW() などの関数を組み合わせて作るのが一般的です
| 演算子 | 説明 | 使用例 | 使用例の説明 |
| GETDATE() | 現在の時刻 – MicrosoftSQLサーバー | [販売日] > DATEADD(month, -1, GETDATE() ) | 販売日が現在のデータ |
| SYSDATE() | 現在の時刻 – ORACLE | [販売日] > DATEADD(month, -1, SYSDATE() ) | 販売日が現在のデータ |
| NOW() | 現在の時刻 – MySQL | [販売日] > DATEADD(month, -1, NOW() ) | 販売日が現在のデータ |
| DATEADD | 日付時刻を追加・差分 | [販売日] > DATEADD(month, -3, NOW) | 販売日が現在より3ヶ月前 |
| DATEDIFF | 日付の差を指定した単位で | DATEDIFF(month,[販売日],’03/01′) | [販売日]と3/1の差が何ヶ月か |
サンプル
販売日が1/1から現在までのデータをとってくる場合
| テーブル名: product | ||||||||
| 商品番号 | カテゴリー | 商品名 | サイズ | 価格 | 販売日 | |||
| 1 | P1001 | コーヒー | ドリップコーヒー | ショート | 290 | 2021/01/01 | ||
| 2 | P1002 | コーヒー | ドリップコーヒー | トール | 330 | 2021/02/01 | ||
| 3 | P1003 | コーヒー | ドリップコーヒー | グランデ | 370 | 2021/03/01 | ||
| 4 | P1004 | コーヒー | ドリップコーヒー | ベンティ | 410 | 2021/04/01 | ||
GETDATE, SYSDATE, NOW
現在の時刻・日付をとってきます
使うデータベースによりコマンドが違ってきます
GETDATEはMicrosoftSQLサーバー、SYSDATEはOracle、NOWはMySQLで使用されます
レポートなど動的に、日付を使うケースで使われます
(SQLiteはデータタイプには日時の考え方が無いので時間は一般的に文字列として扱われます)
DATEADD
DATEADD(単位, 数, 日時)
DATEADDは３つの値をとります
「日時」に「数」「単位」を足す、というコマンドです
単位は、下記の一覧の値を指定します
数は、数を指定します
日時は、元になる日時を指定します
よく使うパターンとしては
DATEADD(month, 3, GETDATE() ) : 「現在から3ヶ月後」
DATEADD(month, -3, GETDATE() ) : 「現在から3ヶ月前」
DATEADDはAdd(追加)するコマンドなので
3ヶ月前のように、引算する場合はマイナスの数(この場合-3)を指定してあげます
| 単位 | 説明 |
| year ,yy, yyyy | 年 |
| month ,mm, m | 月 |
| day ,dd, d | 日 |
| hour ,hh | 時 |
| minute ,mi, n | 分 |
| second ,ss, s | 秒 |
DATEDIFF
DATEDIFF(単位, 日時A, 日時B)
DATEDIFFは３つの値をとります
「日時A」と「日時B」の「単位」差を出す、というコマンドです
DATEDIFF(day, ’12/31′, GETDATE())
とすると年末までの残りの日数がとれます
DATEDIFF(week, ’12/31′, GETDATE())
とすると年末までの残り何週かがとれます
クイズ
下記のテーブルから
「過去2ヶ月で、5000円以上で、商品名に「ドリップ」を含んだ商品を買ってくれたお客さん」を探してみてください
| テーブル名: product | ||||||||
| 顧客番号 | 氏名 | 商品名 | サイズ | 価格 | 販売日 | |||
| 1 | C1001 | 真弓あきこ | ドリップコーヒー – ダークロースト | ショート | 2900 | 2021/01/01 | ||
| 2 | C1002 | 北村てるこ | ドリップコーヒー – ダークロースト | トール | 3300 | 2021/02/01 | ||
| 3 | C1003 | ランディばす | ドリップコーヒー – ダークロースト | グランデ | 9700 | 2021/03/01 | ||
| 4 | C1004 | 掛布まさこ | ドリップコーヒー – ダークロースト | ベンティ | 8100 | 2021/04/01 | ||
| 5 | C1010 | 岡田あきこ | カフェミスト | ショート | 3400 | 2021/01/01 | ||
| 6 |
C1011
|
佐野のりこ | カフェミスト | トール | 5400 | 2021/02/01 | ||
| 7 | C1012 | 平田かつこ | カフェミスト | グランデ | 9290 | 2021/03/01 | ||
| 8 | C1013 | 吉竹てるこ | カフェミスト | ベンティ | 4600 | 2021/04/01 | ||
