One IT Thing

IT業界で飯を食う為の学習系雑記

PostgreSQL

PostgreSQL エポックタイムカラムを今月初~今月末で範囲検索

投稿日:

エポックタイムは1970年1月1日 0時0分0秒から現在までの秒数。

PostgreSQLではこれを人間に読みやすいTIMESTAMP型(’2019-09-04 00:00:00’)で表現するケースもあれば、そのまま秒数カウント(BIGINT型)で使用する場合もあります。

エポックタイム数値とTIMESTAMP型ではBETWEENで比較出来ないので型を併せて日付範囲検索していきます。

TIMESTAMP型、エポックタイム数値の相互変換

公式ドキュメントでEXTRACT関数、TO_TIMESTAMP関数が紹介されているのでこれを使って変換してみます。

タイムスタンプ → エポックタイム

extract関数は指定したタイムスタンプ表記から時間(hour)や分(min)を指定することで特定フィールドの値を取ることが出来、「epoch」を指定するとエポックタイムが返却されます。

postgres=# SELECT EXTRACT(EPOCH FROM TIMESTAMP '2019-09-03');
 date_part
------------
 1567468800
(1 行)

エポックタイム → タイムスタンプ

DBにエポックタイムで入ってる場合は一度to_timestamp関数でタイムスタンプ型に変換すると月の分かれ目が分かるので範囲検索がし易くなります。

postgres=# SELECT TO_TIMESTAMP(1567468800);
      to_timestamp
------------------------
 2019-09-03 09:00:00+09
(1 行)

ミリ秒まで入ったエポックタイムの場合

題材はmattermostのメッセージ投稿時刻。ミリ秒として下三桁が追加されているので、

mattermost_production=# SELECT createat FROM posts;
   createat
---------------
 1566834493673
 1566834493723
 1566838470434

/ 1000して秒に変換。

mattermost_production=# SELECT createat/1000 AS createat FROM posts;
   column
------------
 1566834493
 1566834493
 1566838470

mattermost_production=# SELECT TO_TIMESTAMP(createat/1000) AS createat FROM posts;
        createat
------------------------
 2019-08-26 15:48:13+00
 2019-08-26 15:48:13+00
 2019-08-26 16:54:30+00

月初、月末を求める

詳細は公式ドキュメント参照。

月初

現在時刻から今月の月を出す。

SELECT DATE_TRUNC(‘month’, now());

postgres=# SELECT DATE_TRUNC('month', now()) AS begginingofmonth;
    begginingofmonth
------------------------
 2019-09-01 00:00:00+09
(1 行)

月末

今月に一月足して来月初を出し、-1日して今月末を求める。

SELECT DATE_TRUNC(‘month’, now()) + ‘1 month’ +’-1 Day’;

postgres=# SELECT DATE_TRUNC('month', now()) + '1 month' +'-1 Day' AS endofmonth;
       endofmonth
------------------------
 2019-09-30 00:00:00+09
(1 行)

エポックタイムを今月で範囲検索

エポックタイム→TIMESTAMP型変換、月末~月初の範囲検索の仕方が分かったので、mattermostの今月発言をピックアップしてみます。

SELECT message FROM posts WHERE TO_TIMESTAMP(createat / 1000) BETWEEN DATE_TRUNC(‘month’, NOW()) AND DATE_TRUNC(‘month’, NOW()) + ‘1 month’ + ‘-1 Day’;

mattermost_production=# SELECT
mattermost_production-#     message
mattermost_production-# FROM
mattermost_production-#     posts
mattermost_production-# WHERE
mattermost_production-#     TO_TIMESTAMP(createat / 1000) BETWEEN DATE_TRUNC('month', NOW()) AND DATE_TRUNC('month', NOW()) + '1 month' + '-1 Day';
                           message
--------------------------------------------------------------
 developer2 joined the team.
 developer2 joined the channel.
 はじめまして!developer2と申します!
 こんにちは!まずはgitlabからtestprojectをcloneしてください。
 分からないことが有ったら聞いてくださいね。

今月のMattermost発言が取れました。

まとめ

エポックタイムカラムをタイムスタンプとして判断して、今月の範囲検索が出来ました。

因みにBIGINT(8byte : 9,223,372,036,854,775,807)で表現できる時間は3,000億年だそうです。

変数容量の心配 は暫くしなくて大丈夫そうです。

-PostgreSQL

執筆者:

関連記事

ER図自動生成ツール「SchemaSpy」でdvdrentalを可視化

SchemaSpyを使うとDBからデータを吸い上げてテーブルの相関をビジュアライズしてくれます。 本来ER図からDDLを生成出来るようにしておくべきで、出来れば使わない方がいいチート系のツールですがC …

無料で自由に使える日本全国の企業データベースをPostgreSQLで作る

開発するシステムよっては一覧から企業を選択する機能があるかも知れません。「企業データベース」でググると沢山の商用企業データベースが出てきます。 「でも・・・お高いんでしょう?」 はい。下手をすると家が …

PostgreSQL11に一般ユーザ権限でサンプルDB「dvdrental」をpg_restore

データベースインストール後にサンプルデータを投入することで、DBをバックエンドにして動作する他ミドルの検証が楽になったりすることがあります。 それぞれのデータベースで代表的なサンプルデータセットの存在 …

開発用PostgreSQLをインストールした後の外部接続、ユーザ作成、認証周りの設定

ローカル開発用にPostgreSQLを入れた後、開発や分析をし易くする為に外部ホストからパスワード付き接続出来るようにしていきます。 また、postgresスーパーユーザは権限が強すぎて開発アプリから …

pgcryptoの有効化と共通鍵暗号の動作確認

PostgreSQLで「pgcrypto」拡張機能を有効にすると、PostgreSQLに保存するデータを暗号化出来るようになります。 INSERTする際、共通鍵暗号はpgp_sym_encrypt関数 …

 

shingo.nakanishi
 

東京在勤、職歴20年越え中年ITエンジニアです。まだ開発現場で頑張っています。

19歳(1996年)から書き始めた個人日記が5,000日を超え、残りの人生は発信をして行きたいと思い、令和元日からこのサイトを開始しました。勉強と試行錯誤をしながら、自分が経験したIT関連情報を投稿しています。

私と同じく、今後IT業界で生計を立てて行きたいと考えている方や、技術共有したいけどフリーランスで孤独、といった方と一緒に成長、知識共有して行けたら楽しいな、と思っています。