第１０章：日時型の処理１(SQL復習)

                                                                                                                     by『前処理大全』

• 内容
• 10-1 日時型、日付型への変換
• 10-2 年／月／日／時刻／分／秒／曜日への変換
• 10-3 日時差への変換

10-1 日時型、日付型への変換

例）対象データはホテルの予約レコード。予約テーブルのreserve_datetimeを日時型と日付型に変換。また、checkin_dataとcheckin_timeをあわせて日時型に変換し、checkin_dateを日付型に変換する。

• SQL
• SQLでは、日時型(timestamp)と日付型(Date)をサポートしている。また、timestampzというtimestampにtimezone情報を付与したデータ型がある。
• 
  SELECT
  　　-- timestamptzに変換
  　　TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS')
  　　　　　　　　　　　　　　　　AS reserve_datetime_timestamptz,
  
  　　-- timestamptzに変換後に、timestampに変換
  　　CAST(
  　　　TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS')
  　　　　　　　　　　　　　　　　　　　　　　　　　　　AS TIMESTAMP
  　　) AS resrve_datetime_timestamp,
  
  　　-- 日付と時刻の文字を結合してから、TIMESTAMPに変換
  　　TO_TIMESTAMP(checkin_date || checkin_time, 'YYYY-MM-DDHH24:MI:SS')
  　　　　　　　　　　　　　　　　　　　　　　　　　　　AS chechin_timestamptz,
  
  　　--  日時文字列を日付型に変換（時刻情報は変換後削除されている）
  　　TO_DATA(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS')
  　　　　　　　　　　　　　　　　　　　　　　　　　　　AS reserve_date,
  
  　　-- 日付文字列を日付型に変換
  　　TO_DATE(checkin_date, 'YYYY-MM-DD') AS checkin_date
  
  FROM
  　　work.reserve_tb;
  
  TO_TIMESTAMP関数は、文字列をtimestamptzに変換する関数。
  timestamp型に変換したい場合は、CAST関数にTIMESTAMPを指定する。
• Python
  
• 大体、datetime64[ns]型で事足りる。
• 
  # to_datetime関数で、datetime64[ns]型に変換
  pd.to_datetime(reserve_tb['reserve_datetime'], format='%Y-%m-%d %H:%M:%S')
  pd.to_datetime(reserve_tb['checkin_date'] + reserve_tb['checkin_time'],
  　　　　　　　　　　　　　　　　　　　　　　　format='%Y-%m-%d%H:%M:%S')
  
  # datetime64[ns]型から日付情報を取得
  pd.to_datetime(reserve_tb['reserve_datetime'],
  　　　　　　　　　　　format='%Y-%m-%d%H:%M:%S').dt.date
  pd.to_datetime(reserve_tb['checkin_date'], format='%Y-%m-%d').dt.date

10-2 年／月／日／時刻／分／秒／曜日への変換

例）ホテルの予約レコードにおいて、予約テーブルのreserve_datetimeから、年／月／日／時刻／分／秒を取り出す。また、”年-月-日 時:分:秒" の文字列に変換する。

• SQL
• SQLでは、DATE_PART関数で特定の日付要素を取得する方法と、TO_CHAR関数で指定した文字列に変換する方法がある。特定の日付要素を１つ取り出すだけならば、DATE_PART関数を使うほうが楽。
• 
  WITH tmp_log AS(
  　　SELECT
  　　　　CAST(
  　　　　　TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS')
  　　　　　　            　　　　　　　　　　　　　　　　　　　　　　AS TIMESTAMP
  　　　　) AS reserve_datetime_timestamp
  　　FROM
  　　　　work.reserve_tb
  )
  SELECT
  　　-- DATE型もDATE_PART関数は利用可
  　　-- TIMESTAMPTZ型は、DATA_PART関数は利用不可
  　　
  　　-- 年を取得
  　　DATE_PART(year, reserve_datetime_timestamp) AS reserve_datetime_year,
  
  　　-- 月を取得
  　　DATE_PART(month, reserve_datetime_timestamp) AS reserve_datetime_month,
  
  　　-- 日を取得
  　　DATE_PART(day, reserve_datetime_timestamp) AS reserve_datetime_day,
  
  　　-- 曜日(0は日曜日、1は月曜日)
  　　DATE_PART(dow, reserve_datetime_timestamp) AS reserve_datetime_day,
  
  　　-- 時刻の時を取得
  　　DATE_PART(hour, reserve_datetime_timestamp) AS reserve_datetime_hour,
  
  　　-- 時刻の分を取得
  　　DATE_PART(minute, reserve_datetime_timestamp) AS reserve_datetime_minute,
  
  　　-- 時刻の秒を取得
  　　DATE_PART(second, reserve_datetime_timestamp) AS reserve_datetime_second,
  
  　　-- 指定したフォーマットの文字列に変換
  　　TO_CHAR(reserve_datetime_timestamp, 'YYYY-MM-DD HH24:MI:SS')
  　　　　　　　　　　　　　　　　　　　　　　　　　　　AS reserve_datetime_char
  FROM
  　　tmp_log;
  
  DATE型を指定して、hour, minuteやsecondを取り出そうとするとエラーにならず0を返してくるので注意。
• Python
• datetime64[ns]型は、日時要素をデータ内に保持しているので、直接要素を取得できる。
• 
  # reserve_datetimeをdatetime64[ns]型に変換
  reserve_tb['reserve_datetime'] = 
  　　pd.to_datetime(reserve_tb['reserve_datetime'], format='%Y-%m-%d %H:%M:%S')
  
  # 年を取得
  reserve_tb['reserve_datetime'].dt.year
  
  # 月を取得
  reserve_tb['reserve_datetime'].dt.month
  
  # 日を取得
  reserve_tb['reserve_datetime'].dt.day
  
  # 曜日(0=日曜日、1=月曜日)を数値で取得
  reserve_tb['reserve_datetime'].dt.dayofweek
  
  # 時刻の時を取得
  reserve_tb['reserve_datetime'].dt.hour
  
  # 時刻の分を取得
  reserve_tb['reserve_datetime'].dt.minute
  
  # 時刻の秒を取得
  reserve_tb['reserve_datetime'].dt.second
  
  # 指定したフォーマットの文字列に変換
  reserve_tb['reserve_datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')

10-3 日時差への変換

日時差（年数／月数／週数／日数／時間差）の取得。例えば、Webにアクセスしてから商品の購入までにかかる時間や、宿の予約日と宿泊日の日数差などを知りたい時によく求める。

差分を求めるときに、明確に定義を決めないと値の意味がわからなくなるので注意。

例えば、12:45:59と12:46:00の分の差分を求めたい時、秒以下を無視して46-45=1分と考えるべきか、秒以下を考慮して(60-59)/60=0.016666...分と考えるべきかは、ケースにより異なる。ただし、月と年単位は、必ず日以下を無視して計算する。なぜなら、月も年も長さが一定ではなく、単位として利用できないため。うるう年の影響により、年によって日数は異なり、月も人数が異なるため。

例）ホテルの予約レコードが対象。予約テーブルの予約日時とチェックイン日時の年／月／日／時／分／秒の差分を計算する。ただし、年／月は、月／日以下の要素を考慮せずに差分を計算し、時／分／秒は単位に換算して差分を計算する。

• SQL
• SQLでは、DATEDIFF関数が便利。ただし、DATEDIFF関数は指定した単位以下の日時要素は切り捨てて計算する。（2015年12月31日と2016年1月1日の年差分は1年、2016年1月1日と2016年12月31日の年差分は0年となる。）
• 
  WITH tmp_log AS(
  　　SELECT
  　　　-- reserve_datetimeをTIMESTAMP型へ変換
  　　　CAST(
  　　　　TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS')
  　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AS TIMESTAMP)
  　　　) AS reserve_datetime,
  
  　　　-- checkin_datetimeをTIMESTAMP型へ変換
  　　　CAST(
  　　　TO_TIMSESTAMP(checkin_date || checkin_time, 'YYYY-MM-DDHH24:MI:SS' )
  　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AS TIMESTAMP)
  　　　) AS checkin_datetime
  
  　　FROM
  　　　work.reserve_tb
  )
  SELECT
  　　-- 年の差を計算（月以下の日時要素は考慮しない）
  　　DATEDIFF(year, reserve_datetime, checkin_datetime) AS diff_year,
  
  　　-- 月の差を取得（日以下の日時要素は考慮しない）
  　　DATEDIFF(month, reserve_datetime, checkin_datetime) AS diff_month,
  
  　　-- 日の差分（時間以下の日時要素は考慮しない）
  　　DATEDIFF(day, reserve_datetime, checkin_datetime) AS diff_day,
  
  　　-- 時の差分を計算（分以下の日時要素は考慮しない）
  　　DATEDIFF(hour, reserve_datetime, checkin_datetime) AS diff_hour,
  
  　　-- 分の差分を計算（秒以下の日時要素は考慮しない）
  　　DATEDIFF(minute, reserve_datetime, checkin_datetime) AS diff_minute,
  
  　　-- 日単位で差分を計算
  　　CAST(DATEDIFF(second, reserve_datetime, checkin_datetime) AS FLOAT) /
  　　　　　　　　　　　　　　　　　　　　　　　　　　　(60 * 60 * 24) AS diff_day2,
  
  　　-- 時間単位で差分を計算
  　　CAST(DATEDIFF(second, reserve_datetime, checkin_datetime) AS FLOAT) /
  　　　　　　　　　　　　　　　　　　　　　　　　　　　　(60 * 60) AS diff_hour2,
  
  　　-- 分単位で差分を計算
  　　CAST(DATEDIFF(second, reserve_datetime, checkin_datetime) AS FLOAT) /
  　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　60 AS diff_minute2,
  
  　　-- 秒単位で差分を計算
  　　DATEDIFF(second, reserve_datetime, checkin_datetime) AS diff_second
  
  FROM
  　　tmp_log;
  
  
• Python
• datetime64[ns]型同士を引き算すると、timedelta64[ns]型の日時分秒に分解された差分のデータが返される。また、差分を日／時／分／秒単位に変換したい場合は、astypeでtimedelta64[D/h/m/s]型に変換することで実現できる。年／月の差分を計算するときは、年／月の日時要素を取り出して計算する必要がある。
• 
  # reserve_datetimeをdatetime64[ns]型に変換
  reserve_tb['reserve_datetime'] = \
  　　pd.to_datetime(reserve_tb['reserve_datetime'], format='%Y-%m-%d %H:%M:%S')
  
  # checkin_datetimeをdatetime64[ns]型に変換
  reserve_tb['checkin_datetime'] = \
  　　pd.to_datetime(reserve_tb['checkin_datetime'] + reserve_tb['checkin_time'],
  　　　　　　　　　　　　　　　　　　　　　　　　format='%Y-%m-%d%H:%M:%S')
  
  # 年の差分を計算（月以下の日時要素は考慮しない）
  reserve_tb['reserve_datetime'].dt.year - reserve_tb['checkin_datetime'].dt.year
  
  # 月の差分を取得（日以下の日時要素は考慮しない）
  (reserve_tb['reserve_datetime'].dt.year * 12 + \ 
  　　　　　　　　　　　 reserve_tb['reserve_datetime'].dt.month)
  - (reserve_tb['checkin_datetime'].dt.year * 12 + \
  　　　　　　　　　　　 reserve_tb['checkin_datetime'].dt.month)
  
  # 日単位で差分を計算
  (reserve_tb['reserve_datetime'] - reserve_tb['checkin_datetime']) \
  　　　　　　　　　　　　　　　　　　　　　　　　　　　　.astype('timedelta64[D]')
  
  # 時単位で差分を計算
  (reserve_tb['reserve_datetime'] - reserve_tb['checkin_datetime']) \
  　　　　　　　　　　　　　　　　　　　　　　　　　　　　.astype('timedelta64[h]')
  
  # 分単位で差分を計算
  

  (reserve_tb['reserve_datetime'] - reserve_tb['checkin_datetime']) \

  　　　　　　　　　　　　　　　　　　　　　　　　　　　　.astype('timedelta64[m]')
  
  # 秒単位で差分を計算
  

  (reserve_tb['reserve_datetime'] - reserve_tb['checkin_datetime']) \

  　　　　　　　　　　　　　　　　　　　　　　　　　　　　.astype('timedelta64[s]')
  
  timedelta64[D/h/m/s]型により差分を日／時／分／秒単位に変換した場合、小数点以下は切り上げた結果が返ってくる。そのため、SQLとは異なる結果になる。たとえば、差分が2日と3時間のときに日単位に変換すると、3(日)が返ってくる。