知っておけば役立つ！PostgreSQLでタイムスタンプを切り上げ・切り下げる4つの方法

2024-04-02

PostgreSQLでタイムスタンプを切り上げ・切り下げする方法

date_trunc() 関数は、指定された日付型を指定された精度で切り捨てます。タイムスタンプを切り上げ・切り下げするには、以下のように date_part と interval を使用します。

切り上げ

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59') + interval '1 minute';

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59');

floor() と ceil() 関数を使う

floor() 関数は数値を切り捨て、ceil() 関数は数値を切り上げます。タイムスタンプを切り上げ・切り下げするには、以下のように extract() 関数と組み合わせて使用します。

SELECT timestamp '2024-03-27 00:37:59' + interval '1 minute' * (ceil(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0) - extract(minute from timestamp '2024-03-27 00:37:59'));

SELECT timestamp '2024-03-27 00:37:59' - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59') / 1.0 - floor(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0));

CASE 式を使って、分が30秒以上であれば切り上げ、30秒未満であれば切り下げるようにすることもできます。

SELECT CASE
  WHEN extract(minute from timestamp '2024-03-27 00:37:59') >= 30
  THEN timestamp '2024-03-27 00:38:00'
  ELSE timestamp '2024-03-27 00:37:00'
END;

以下のサンプルコードは、上記の3つの方法をすべて試して、結果を確認するものです。

-- 1. date_trunc() 関数を使う

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59') + interval '1 minute';
SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59');

-- 2. floor() と ceil() 関数を使う

SELECT timestamp '2024-03-27 00:37:59' + interval '1 minute' * (ceil(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0) - extract(minute from timestamp '2024-03-27 00:37:59'));
SELECT timestamp '2024-03-27 00:37:59' - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59') / 1.0 - floor(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0));

-- 3. CASE 式を使う

SELECT CASE
  WHEN extract(minute from timestamp '2024-03-27 00:37:59') >= 30
  THEN timestamp '2024-03-27 00:38:00'
  ELSE timestamp '2024-03-27 00:37:00'
END;



-- PostgreSQL 14 以降

SELECT
  timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles'::text,
  date_trunc('minute', timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles')::text,
  date_trunc('minute', timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles') + interval '1 minute'::text,
  date_trunc('minute', timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles') - interval '1 minute'::text;

-- PostgreSQL 13 以前

SELECT
  timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles'::text,
  (timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles')::date::timestamp - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles') / 1.0)::int + interval '1 minute'::text,
  (timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles')::date::timestamp - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles') / 1.0)::int::text,
  (timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles')::date::timestamp - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles') / 1.0)::int - interval '1 minute'::text;

2024-03-27 00:37:59
2024-03-27 00:37:00
2024-03-27 00:38:00
2024-03-27 00:36:00

このサンプルコードは、タイムゾーン America/Los_Angeles を使用して、タイムスタンプ 2024-03-27 00:37:59 を切り上げ・切り下げます。

解説

  • 最初の列は、元のタイムスタンプです。
  • 2番目の列は、date_trunc() 関数を使って切り捨てたタイムスタンプです。

PostgreSQL 14 以降では、date_trunc() 関数でタイムゾーンを直接指定できるようになりました。

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles');

PostgreSQL 13 以前では、date_trunc 関数でタイムゾーンを直接指定できません。そのため、以下のように extract() 関数と interval を組み合わせて使用します。

(timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles')::date::timestamp - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59' AT TIME ZONE 'America/Los_Angeles') / 1.0)::int + interval '1 minute'


PostgreSQLでタイムスタンプを切り上げ・切り下げする方法

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59') + interval '1 minute';

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59');

SELECT timestamp '2024-03-27 00:37:59' + interval '1 minute' * (ceil(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0) - extract(minute from timestamp '2024-03-27 00:37:59'));

SELECT timestamp '2024-03-27 00:37:59' - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59') / 1.0 - floor(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0));

SELECT CASE
  WHEN extract(minute from timestamp '2024-03-27 00:37:59') >= 30
  THEN timestamp '2024-03-27 00:38:00'
  ELSE timestamp '2024-03-27 00:37:00'
END;

-- 1. date_trunc() 関数を使う

SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59') + interval '1 minute';
SELECT date_trunc('minute', timestamp '2024-03-27 00:37:59');

-- 2. floor() と ceil() 関数を使う

SELECT timestamp '2024-03-27 00:37:59' + interval '1 minute' * (ceil(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0) - extract(minute from timestamp '2024-03-27 00:37:59'));
SELECT timestamp '2024-03-27 00:37:59' - interval '1 minute' * (extract(minute from timestamp '2024-03-27 00:37:59') / 1.0 - floor(extract(minute from timestamp '2024-03-27 00:37:59') / 1.0));

-- 3. CASE 式を使う

SELECT CASE
  WHEN extract(minute from timestamp '2024-03-27 00:37:59') >= 30
  THEN timestamp '2024-03-27 00:38:00'
  ELSE timestamp '2024-03-27 00:37:00'
END;

その他の方法

上記の方法以外にも、以下のような方法でタイムスタンプを切り上げ・切り下げることができます。

  • to_char(

postgresql

Rails + Postgres でデータベース削除エラー "database is being accessed by other users" の解決策

Railsアプリケーションで rake db:drop コマンドを実行しようとすると、以下のエラーが発生する場合があります。このエラーは、削除しようとしているデータベースが現在使用されているため発生します。具体的には、アプリケーション、データベース管理ツール、または他のプロセスによってデータベースへの接続が開かれている可能性があります。...

 
ruby on rails postgresql Rails + Postgres でデータベース削除エラー "database is being accessed by other users" の解決策

PostgreSQL テーブルの age 列のデータ型を integer に変更する方法

基本的な構文:例:この例では、customersテーブルのage列のデータ型をintegerに変更します。データ型変更時の注意点:新しいデータ型は、既存のデータと互換性がある必要があります。列に格納されているデータが新しいデータ型に収まらない場合、エラーが発生します。...

 
sql postgresql psql PostgreSQL テーブルの age 列のデータ型を integer に変更する方法

PostgreSQLシーケンスの値を手動で変更する：pgAdmin、psql、PL/pgSQLの活用方法

PostgreSQLでは、シーケンスと呼ばれるオブジェクトを使用して、テーブルの列に自動的に採番される値を生成することができます。シーケンスは、データベース内で一意の識別子を作成するために役立ちます。シーケンスは、通常、CREATE SEQUENCEコマンドを使用して作成されます。このコマンドには、シーケンスの名前、開始値、および増分値を指定するオプションが含まれます。...

 
sql postgresql ddl PostgreSQLシーケンスの値を手動で変更する：pgAdmin、psql、PL/pgSQLの活用方法

SQLAlchemy: group_by() と count() 関数で複数列の重複カウントを効率的に取得

問題の定義複数の列で重複カウントを取得したい場合、単一の列でカウントするよりも複雑になります。これは、複数の列でグループ化し、各グループ内の重複カウントを数える必要があるためです。SQLAlchemy では、group_by() と count() 関数を使用して、複数の列で重複カウントを取得できます。以下の例は、customers テーブルの city 列と state 列で重複カウントを取得する方法を示しています。...

 
postgresql count sqlalchemy SQLAlchemy: group_by() と count() 関数で複数列の重複カウントを効率的に取得

異なる PostgreSQL バージョン間で pg_dump と pg_restore を使用する際の落とし穴と回避策

pg_dump と pg_restore は、PostgreSQL データベースのバックアップとリストアに不可欠なツールです。しかし、異なるメジャーバージョン間でこれらのツールを使用する場合、互換性の問題が発生する可能性があります。このガイドでは、異なる PostgreSQL メジャーバージョン間で pg_dump と pg_restore を安全かつ効果的に使用する方法について説明します。...

 
postgresql 異なる PostgreSQL バージョン間で pg_dump と pg_restore を使用する際の落とし穴と回避策

SQL SQL SQL SQL Amazon で見る


PostgreSQLで処理速度とストレージスペースを節約：タイムスタンプを5分単位に切り捨ててデータベース運用を最適化
PostgreSQLで処理速度とストレージスペースを節約：タイムスタンプを5分単位に切り捨ててデータベース運用を最適化

タイムスタンプは、時間と日付を記録する便利なデータ型ですが、分析や処理を行う際に、精度が過剰な場合があります。特に、5分単位のデータで十分な場合、ミリ秒やマイクロ秒単位の精度を維持すると、処理速度が低下したり、ストレージスペースを無駄に消費したりする可能性があります。

PostgreSQLでタイムスタンプのミリ秒部分を切り捨てる3つの方法とは？
PostgreSQLでタイムスタンプのミリ秒部分を切り捨てる3つの方法とは？

date_trunc() 関数は、指定された時刻精度でタイムスタンプを切り捨てることができます。ミリ秒部分を切り捨てるには、'second' を精度として指定します。利点:シンプルで分かりやすい構文他の精度での切り捨てにも使える小数点以下の値が切り捨てられるため、情報損失が発生する可能性がある