pg_extra_time
Some date time functions and operators that,
Repository
bigsmoke/pg_extra_time
https://github.com/bigsmoke/pg_extra_time
Source
pg_extra_time-2.0.0.tar.gz
pg_extra_time-2.0.0.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_extra_time | 2.0.0 | UTIL | PostgreSQL | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 4220 | pg_extra_time | No | Yes | No | Yes | Yes | Yes | - |
| Related | pgsql_tweaks periods temporal_tables pg_cron gzip bzip zstd http |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 2.0.0 | 1817161514 | pg_extra_time | - |
| RPM | PGDG | 2.0.0 | 1817161514 | pg_extra_time_$v | - |
| DEB | PIGSTY | 2.0.0 | 1817161514 | postgresql-$v-pg-extra-time | - |
Build
You can build the DEB packages for pg_extra_time using pig build:
pig build pkg pg_extra_time # build DEB packages
Install
You can install pg_extra_time directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_extra_time; # Install for current active PG version
pig ext install -y pg_extra_time -v 18 # PG 18
pig ext install -y pg_extra_time -v 17 # PG 17
pig ext install -y pg_extra_time -v 16 # PG 16
pig ext install -y pg_extra_time -v 15 # PG 15
pig ext install -y pg_extra_time -v 14 # PG 14
dnf install -y pg_extra_time_18 # PG 18
dnf install -y pg_extra_time_17 # PG 17
dnf install -y pg_extra_time_16 # PG 16
dnf install -y pg_extra_time_15 # PG 15
dnf install -y pg_extra_time_14 # PG 14
apt install -y postgresql-18-pg-extra-time # PG 18
apt install -y postgresql-17-pg-extra-time # PG 17
apt install -y postgresql-16-pg-extra-time # PG 16
apt install -y postgresql-15-pg-extra-time # PG 15
apt install -y postgresql-14-pg-extra-time # PG 14
Create Extension:
CREATE EXTENSION pg_extra_time;
Usage
pg_extra_time: Extra date/time functions and operators for PostgreSQL
Convert to Seconds (float)
SELECT to_float('1970-01-01 00:00:00+0'::timestamptz); -- 0.0
SELECT to_float('1 day 1 sec'::interval); -- 86401.0
SELECT to_float('[2024-06-06 05:58:00,2024-06-06 06:00:10]'::tstzrange); -- 130.0
Cast syntax also works:
SELECT '1970-01-01 01:03:01+00'::timestamptz::float; -- 3181.00
SELECT '1 day 1 sec 200 ms'::interval::float; -- 86401.2
Convert to Days
SELECT days('[2024-06-06,2024-06-08 06:00]'::tstzrange); -- 3.25 (fractional days)
SELECT whole_days('[2024-06-06,2024-06-08 18:00]'::tstzrange); -- 2 (whole days only)
SELECT days('10 days 12 hours'::interval); -- 10.5
SELECT whole_days('10 days 20 hours'::interval); -- 10
Extract Interval from Range
SELECT to_interval('[2024-01-01,2024-01-05]'::tstzrange); -- 4 days
Each Functions (generate series from ranges)
SELECT * FROM each_subperiod('[2024-01-01,2024-01-05]'::tstzrange, '1 day'::interval);
Operators
-- Range duration as float (seconds)
SELECT '[epoch,1970-01-01T01:03:01+00]'::tstzrange::float;
-- Interval as float (seconds)
SELECT '10 seconds 100 milliseconds'::interval::float; -- 10.100
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.