PostgreSQLで先週の日曜日から土曜日までの日付でデータ取得したい

PostgreSQLで、先週分のデータを取得したい場合ありますよね？
例えば、単純に７日前〜昨日までとかでデータを取得すると、SQLを実行する日付によって変わってきてしまいます。
なので先週の日曜日から土曜日までを固定でデータを取りたいという場合の対応。

SELECT (now() + CAST('-' || (extract(dow from now()) + 7) || ' day' AS INTERVAL))::DATE;


まず上記で、今日の日付から、先週の日曜日のデータが取得できます。
※extractを使うと日曜日=0,月曜日=1というデータが取得でき、そこに7日を足した分を、頭の’-‘でマイナスを付けて引いています。



同様に、先週の土曜日のデータを取得する方法は以下になります
SELECT (now() + CAST('-' || (extract(dow from now()) + 1) || ' day' AS INTERVAL))::DATE;


単純に+7だったのを、+1に変えてあげればいいですね。



上記データを元に、実際の先週分を取得するSQLサンプルはこちら

SELECT 列名
FROM テーブル名
WHERE
created >= (SELECT (now() + CAST('-' || (extract(dow from now()) + 7) || ' day' AS INTERVAL))::DATE)
AND created < (SELECT (now() + CAST('-' || (extract(dow from now())) || ' day' AS INTERVAL))::DATE)

先週の土曜日までを取得したいのに、+1がなくなっているのは、「今週の日曜より小さいデータ」を取得して「先週分」とするためです。

以上です。自分もたまにしか使わず忘れてしまうので、誰かのご参考になれば！

PostGisを1.5.3から2.0にアップデートしたときに発生したSQLエラー対応

PostGisを1.5.3から2.0にアップデートしたときに、既存のpostgisからデータを取得するSQLで下記のようなエラーが発生。


1. GeometryFromText 指定名称、指定引数型に合う関数がありません。明示的な型キャストが必要かもしれません
2. 関数geometryfromtext(unknown, integer)は存在しません
3. 関数expand_sphere_pseudo(geometry, integer)は存在しません
4. リレーション spatial_ref_sys への権限がありません


もともとPostgis1.5.3で実際に流していたSQLは以下のようなもの

SELECT
/*省略*/
trunc(distance_sphere(A.geo, GeometryFromText('POINT(139.000 35.000)', 4326))) as DISTANCE
FROM
/*省略*/
WHERE
expand_sphere_pseudo(GeomFromText('POINT(139.000 35.000)', 4326), 1000) && A.geo
AND distance_sphere(A.geo, GeometryFromText('POINT(139.000 35.000)', 4326)) < 1000

上記で、半径1000m以内のデータを取得していた。DISTANCEには、現在地からの距離。



1については、そもそもGeometryFromTextという関数がなくなっていた。
Postgis2.0では、ST_GeomFromTextを代わりに利用する模様。


2,3の関数geometryfromtextとdistance_sphereを使って、半径1000ｍ以内のデータを取得しようとしていた処理では
ST_DWithinという関数を使って実現する。


最終的なPostgis2.0に対応したSQLは以下で結果が正常に返ってきた

SELECT
/*省略*/
trunc(ST_Distance(A.geo, ST_GeomFromText('POINT(139.000 35.000)', 4326))) as DISTANCE
FROM
/*省略*/
WHERE
ST_DWithin(A.geo, ST_GeomFromText('POINT(139.000 35.000)', 4326), 1000,true)

リレーション spatial_ref_sys への権限がありません
というエラーについては
/usr/share/pgsql/contrib/postgis-2.0/spatial_ref_sys.sql
上記のSQLを流すことで解決

psql -U postgres db名 -f /usr/share/pgsql/contrib/postgis-2.0/spatial_ref_sys.sql