我ながら分かりにくいタイトルになってしまいましたが、やりたいことはこういうことです:
データベースに Postgres を使っているケースで、テーブルのある bigint 列にそのレコードの作成日時が記録されているものとします(これ自体はそこまで珍しくないと思っています):
上の例では items テーブルの "created" 列が bigint 型で定義されていて、このレコードが作成された日時の(ミリ秒単位の)タイムスタンプ値が格納されているものとします。
そしてこの items テーブルに格納されているレコードから、created の値が(例えば)1月17日のものだけを取り出す、というクエリーを実行するにはどのような SQL にすればよいか、という命題です。
要は facebook の思い出(過去のこの日)機能のような「何年か前の今日と同じ日付に作成したデータ」を取り出したくなることがあるのです。年や時分秒は違ってもいいので、月と日だけが一致している過去のデータを取り出したい、というケースです。日付が文字列で記録されていれば部分一致検索すればいいだけなので難しくはないと思いますが、これを bigint 型のタイムスタンプで格納されている中で実現するにはどのような SQL を実行すればよいか? というのがやりたいことでした。
で、その方法です。PostgreSQL には to_char() という組み込み関数が存在していて、この関数を使うと(PostgreSQL の)タイムスタンプ値をフォーマットを指定した文字列に変換することができます。また to_timestamp() という組み込み関数もあり、こちらは bigint などの値を(PostgreSQL の)timestamp 型に変換してくれます。
この2つの関数を併用して、例えば、
のように実行すると、
さらにおまけを。上の SQL は正しく実行できるのですが、タイムスタンプ値は UTC 時間で計算されるので、このままだと「UTC 時間で1月17日」のデータを取り出すことになります。
これを「日本時間で1月17日」のデータを取り出す場合は9時間のオフセットを考慮して、
のように実行することで実現できます。後はこの "9" の部分をプログラムで動的に取得するとか、外部からパラメータ指定できるようにすれば色々なロケールでの過去の同じ月日のデータを取り出すことができる、ということになります。
自作アプリに思い出機能を実装しようとする時に役立つ情報・・・だと思ってます。
データベースに Postgres を使っているケースで、テーブルのある bigint 列にそのレコードの作成日時が記録されているものとします(これ自体はそこまで珍しくないと思っています):
create table items( id varchar(50) primary key, name varchar(100), created bigint );
上の例では items テーブルの "created" 列が bigint 型で定義されていて、このレコードが作成された日時の(ミリ秒単位の)タイムスタンプ値が格納されているものとします。
そしてこの items テーブルに格納されているレコードから、created の値が(例えば)1月17日のものだけを取り出す、というクエリーを実行するにはどのような SQL にすればよいか、という命題です。
要は facebook の思い出(過去のこの日)機能のような「何年か前の今日と同じ日付に作成したデータ」を取り出したくなることがあるのです。年や時分秒は違ってもいいので、月と日だけが一致している過去のデータを取り出したい、というケースです。日付が文字列で記録されていれば部分一致検索すればいいだけなので難しくはないと思いますが、これを bigint 型のタイムスタンプで格納されている中で実現するにはどのような SQL を実行すればよいか? というのがやりたいことでした。
で、その方法です。PostgreSQL には to_char() という組み込み関数が存在していて、この関数を使うと(PostgreSQL の)タイムスタンプ値をフォーマットを指定した文字列に変換することができます。また to_timestamp() という組み込み関数もあり、こちらは bigint などの値を(PostgreSQL の)timestamp 型に変換してくれます。
この2つの関数を併用して、例えば、
select id, name, created from items where to_char( to_timestamp( created / 1000 ), 'MM-DD' ) = '01-17' order by created desc
のように実行すると、
- まずミリ秒単位の created が created / 1000 によって( timestamp 型と同じ)秒単位になり、
- to_timestamp( created / 1000 ) によってタイプスタンプ型に変換され、更に、
- to_char( to_timestamp( created / 1000 ), 'MM-DD' ) によってタイムスタンプ値が '月-日' というフォーマットの文字列に変換される
さらにおまけを。上の SQL は正しく実行できるのですが、タイムスタンプ値は UTC 時間で計算されるので、このままだと「UTC 時間で1月17日」のデータを取り出すことになります。
これを「日本時間で1月17日」のデータを取り出す場合は9時間のオフセットを考慮して、
select id, name, created from items where to_char( to_timestamp( created / 1000 ) + 9 * 3600, 'MM-DD' ) = '01-17' order by created desc
のように実行することで実現できます。後はこの "9" の部分をプログラムで動的に取得するとか、外部からパラメータ指定できるようにすれば色々なロケールでの過去の同じ月日のデータを取り出すことができる、ということになります。
自作アプリに思い出機能を実装しようとする時に役立つ情報・・・だと思ってます。