Tracのチケットレポートめも

Tracで指定した日付（$KJN)の１週間前から指定日の範囲に終了日が含まれるチケットを抽出したい。
例）2011年01月30日と指定し、2011年01月23日〜30日に終了したチケットを抽出。

参考

• SQLite入門

前提

日付の格納形式

• 予定開始日は、拡張項目（日付項目）日で、ticket_customテーブルのvalue（TEXT型）項目に YYYY/MM/DD形式で格納されている。
• パラメータの日付も、予定開始日の形式とあわせて、YYYY/MM/DD形式で指定する。

日付の加減算やフォーマット変換をする strftime(指定フォーマット, 日付, modifier) 関数について軽くおさらい。

• 書式は、
  • strftime(フォーマット, 日付や時刻, modifier, modifier, ・・・)
• フォーマットの指定方法は、
  • %Y/%m/%d %H:%M:%S など
• 日付と時刻の形式方法は、
  • YYYY-MM-DD HH:MM:SS など
  • 日付のデリミッターは、 '-'
• modifier （日付の加減算など）の指定方法は、
  • '+7 days' など

なので、

'/' を '-' に変換の上、加減算する。

strftime('%Y/%m/%d', replace($KJN, '/', '-'), '-7 days')

全体示すと、こんな感じ。

SELECT id AS ticket

   ,summary as  概要

   ,da.value as 開始日

   ,dc.value as 終了日

FROM ticket t

  LEFT JOIN ticket_custom da ON da.ticket = t.id AND da.name = 'due_assign' 

  LEFT JOIN ticket_custom dc ON dc.ticket = t.id AND dc.name = 'due_close' 

WHERE strftime('%Y/%m/%d', replace($KJN, '/', '-'), '-7 days') <= dc.value

  AND dc.value <= strftime('%Y/%m/%d', replace($KJN, '/', '-'))