timestamp9
timestamp9
timestamp9 : timestamp nanosecond resolution
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 3890 | timestamp9 | timestamp9 | 1.4.0 | TYPE | MIT | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-- | No | Yes | No | Yes | no | no |
| Relationships | |
|---|---|
| See Also | prefix semver unit pgpdf pglite_fusion md5hash asn1oid roaringbitmap |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | MIXED | 1.4.0 | 18 17 16 15 14 | timestamp9 | - |
| RPM | PGDG | 1.4.0 | 18 17 16 15 14 | timestamp9_$v | - |
| DEB | PIGSTY | 1.4.0 | 18 17 16 15 14 | postgresql-$v-timestamp9 | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.3.0 | PGDG 1.3.0 |
el8.aarch64 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.3.0 | PGDG 1.3.0 |
el9.x86_64 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.3.0 | PGDG 1.3.0 |
el9.aarch64 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.3.0 | PGDG 1.3.0 |
el10.x86_64 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 |
el10.aarch64 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 | PGDG 1.4.0 |
d12.x86_64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
d12.aarch64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
d13.x86_64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
d13.aarch64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
u22.x86_64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
u22.aarch64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
u24.x86_64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
u24.aarch64 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 | PIGSTY 1.4.0 |
Source
pig build pkg timestamp9;# build debInstall
Make sure PGDG and PIGSTY repo available:
pig repo add pgsql -u # add both repo and update cacheInstall this extension with pig:
pig install timestamp9;# install via package name, for the active PG version pig install timestamp9 -v 18; # install for PG 18 pig install timestamp9 -v 17; # install for PG 17 pig install timestamp9 -v 16; # install for PG 16 pig install timestamp9 -v 15; # install for PG 15 pig install timestamp9 -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION timestamp9;Usage
timestamp9: nanosecond precision timestamp type for PostgreSQL
The timestamp9 extension provides a nanosecond-precision timestamp type stored as a 64-bit integer (nanoseconds since UNIX epoch).
CREATE EXTENSION timestamp9;Data Type
The timestamp9 type supports timestamps from 1970-01-01 to 2262-04-12 with nanosecond precision.
Input Formats
-- Standard PostgreSQL format SELECT '2019-09-19 08:30:05'::timestamp9; -- Full nanosecond precision with timezone SELECT '2019-09-19 08:30:05.123456789 +0200'::timestamp9; -- Cast from bigint (nanoseconds since epoch) SELECT 1568878205123456789::bigint::timestamp9;Type Conversions
- Cast to/from
timestampandtimestamptz - Cast to/from
date
Nanosecond precision is preserved throughout conversions.
Operators
-- Comparison SELECT '2019-09-19'::timestamp9 > '2019-09-18'::timestamp9; -- true -- Arithmetic with intervals SELECT '2019-09-19 23:00:00.123456789'::timestamp9 + interval '1 day'; -- Subtraction SELECT '2019-09-20'::timestamp9 - '2019-09-19'::timestamp9;Functions
SELECT greatest('2019-09-19'::timestamp9, '2019-09-20'::timestamp9);Index Support
Btree and hash indexes are supported.
Last updated on