Join us on YugabyteDB Community Slack
Star us on
Get Started
Slack
GitHub
Get Started
v2.15 (preview) v2.12 (stable) v2.8 (earlier version) v2.6 (earlier version) v2.4 (earlier version) Unsupported versions
  • YUGABYTEDB CORE
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore distributed SQL
      • 4. Build an application
        • Java
        • Node.js
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
        • Scala
        • Rust
    • Explore
      • SQL features
        • SQL Feature Support
        • PostgreSQL Compatibility
        • Foreign Data Wrappers
        • Schemas and Tables
        • Data Types
        • Data Manipulation
        • Queries and Joins
        • Expressions and Operators
        • Stored Procedures
        • Triggers
        • Advanced features
          • Cursors
          • Table Partitioning
          • Views
          • Savepoints
          • Collations
        • Going beyond SQL
          • Follower reads
          • Tablespaces
        • PostgreSQL extensions
      • Fault tolerance
      • Horizontal scalability
        • Scaling Transactions
        • Sharding Data
      • Transactions
        • Distributed Transactions
        • Isolation Levels
        • Explicit Locking
      • Indexes and Constraints
        • Primary keys
        • Foreign keys
        • Secondary indexes
        • Unique indexes
        • Partial indexes
        • Expression indexes
        • Covering indexes
        • GIN indexes
        • Other constraints
      • JSON support
      • Multi-region deployments
        • Sync replication (3+ regions)
        • Async Replication (2+ regions)
        • Row-Level Geo-Partitioning
        • Read replicas
      • Query tuning
        • Introduction
        • Get query statistics using pg_stat_statements
        • Viewing live queries with pg_stat_activity
        • Analyzing queries with EXPLAIN
        • Optimizing YSQL queries using pg_hint_plan
      • Cluster management
        • Point-in-time recovery
      • Change data capture (CDC)
        • CDCSDK Server
        • Debezium connector
        • Java CDC console
      • Security
      • Observability
        • Prometheus Integration
        • Grafana Dashboard
    • Drivers and ORMs
      • Java
        • Connect an app
        • Use an ORM
      • Go
        • Go drivers
        • Go ORMs
        • Supported versions
      • C#
        • Connect an app
        • Use an ORM
      • NodeJS
        • NodeJS drivers
        • NodeJS ORMs
        • Supported Versions
      • Python
        • Python drivers
        • Python ORMs
        • Supported versions
      • Rust
        • Diesel ORM
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
        • 9. TTL for data expiration
      • Real-world examples
        • E-Commerce app
        • IoT fleet management
      • Explore sample apps
      • Best practices
      • Quality of Service
        • Rate limiting connections
        • Write-heavy workloads
        • Transaction priorities
      • Cloud-native development
        • Codespaces
        • Gitpod
    • Migrate
      • YugabyteDB Voyager
        • Install
        • Migration steps
        • Performance
        • yb-voyager CLI
      • Manual import
        • Convert a PostgreSQL schema
        • Migrate a PostgreSQL application
        • Export PostgreSQL data
        • Prepare a cluster
        • Import PostgreSQL data
        • Verify migration
    • Deploy
      • Deployment checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Single-zone
          • Open Source
          • Amazon EKS
          • Google Kubernetes Engine
          • Azure Kubernetes Service
        • Multi-zone
          • Amazon EKS
          • Google Kubernetes Engine
        • Multi-cluster
          • Google Kubernetes Engine
        • Best practices
        • Connect Clients
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Multi-DC deployments
        • Three+ data center (3DC)
        • Asynchronous replication
        • Read replica clusters
    • Benchmark
      • TPC-C
      • sysbench
      • YCSB
      • Key-value workload
      • Large datasets
      • Scalability
        • Scaling queries
      • Resilience
        • Jepsen testing
      • Performance Troubleshooting
    • Secure
      • Security checklist
      • Enable authentication
        • Enable users
        • Configure client authentication
      • Authentication methods
        • Password authentication
        • LDAP authentication
        • Host-based authentication
        • Trust authentication
      • Role-based access control
        • Overview
        • Manage users and roles
        • Grant privileges
        • Row-level security
        • Column-level security
      • Encryption in transit
        • Create server certificates
        • Enable server-to-server encryption
        • Enable client-to-server encryption
        • Connect to clusters
        • TLS and authentication
      • Encryption at rest
      • Column-level encryption
      • Audit logging
        • Configure audit logging
        • Session-Level Audit Logging
        • Object-Level Audit Logging
      • Vulnerability disclosure policy
    • Manage
      • Backup and restore
        • Export and import
        • Distributed snapshots
        • Point-in-time recovery
      • Migrate data
        • Bulk import
        • Bulk export
      • Change cluster configuration
      • Diagnostics reporting
      • Upgrade a deployment
      • Grow cluster
    • Troubleshoot
      • Troubleshooting
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
        • Recover tserver/master
        • Replace a failed YB-TServer
        • Replace a failed YB-Master
        • Manual remote bootstrap when a majority of peers fail
        • Recover YB-TServer from crash loop
      • Node level issues
        • Check servers
        • Inspect logs
        • System statistics
        • Disk failure
        • Common error messages
    • Contribute
      • Core database
        • Contribution checklist
        • Build the source
        • Configure a CLion project
        • Run the tests
        • Coding style
      • Documentation
        • Docs checklist
        • Docs layout
        • Build the docs
          • Editor setup
        • Edit the docs
          • Docs page structure
          • Widgets and shortcodes
          • Syntax diagrams
        • Style guide
  • YUGABYTEDB ANYWHERE
    • Overview
      • Install
      • Configure
    • Install
      • Prerequisites
      • Prepare the environment
      • Install software
      • Prepare nodes
      • Uninstall software
    • Configure
      • Create admin user
      • Configure cloud providers
      • Configure backup target
      • Configure alerts
    • Create deployments
      • Multi-zone universe
      • Multi-region universe
      • Multi-cloud universe
      • Read replica cluster
      • Asynchronous replication
    • Manage deployments
      • Start and stop processes
      • Eliminate an unresponsive node
      • Recover a node
      • Enable high availability
      • Edit configuration flags
      • Edit a universe
      • Delete a universe
      • Configure instance tags
      • Upgrade YugabyteDB
      • Migrate to Helm 3
    • Back up universes
      • Configure backup storage
      • Back up universe data
      • Restore universe data
      • Schedule data backups
    • Security
      • Security checklist
      • Configure ports
      • LDAP authentication
      • Authorization
      • Create a KMS configuration
      • Enable encryption at rest
      • Enable encryption in transit
      • Network security
    • Alerts and monitoring
      • Alerts
      • Live Queries dashboard
      • Slow Queries dashboard
    • Troubleshoot
      • Install and upgrade issues
      • Cloud provider configuration issues
      • Universe issues
    • Administer
      • Back up YugabyteDB Anywhere
      • Configure authentication
    • Upgrade
      • Upgrade Kubernetes installation
      • Upgrade using Replicated
  • YUGABYTEDB MANAGED
    • Overview
    • Quick start
      • Create a Sandbox cluster
      • Connect to the cluster
      • Explore distributed SQL
      • Build an application
        • Before you begin
        • Java
        • Go
        • Python
        • Node.js
        • C
        • C++
        • C#
        • Ruby
        • Rust
        • PHP
    • Deploy clusters
      • Plan your cluster
      • Choose a topology
      • VPC network
        • Overview
        • VPCs
        • Peering connections
        • Create a VPC Network
      • Create your cluster
        • Sandbox
        • Single region
        • Replicate across regions
    • Secure clusters
      • IP allow lists
      • Database authorization
      • Add database users
      • Encryption in transit
      • Audit account activity
    • Connect to clusters
      • Cloud Shell
      • Client shell
      • Connect applications
    • Alerts and monitoring
      • Alerts
      • Performance metrics
      • Live queries
      • Slow queries
      • Performance Advisor
      • Cluster activity
    • Manage clusters
      • Scale and configure clusters
      • Backup and restore
      • Maintenance windows
      • Create extensions
    • Administration and billing
      • Manage account access
      • Manage API keys
      • Manage billing
      • Cluster costs
    • Example applications
      • Connect a Spring application
      • Connect a YCQL Java application
      • Hasura Cloud
      • Deploy a GraphQL application
    • Security architecture
      • Security architecture
      • Shared responsibility model
    • Troubleshoot
    • YugabyteDB Managed FAQ
    • What's new
  • INTEGRATIONS
    • Apache Kafka
    • Apache Spark
    • Camunda
    • Debezium
    • Django REST framework
    • Entity Framework
    • Flyway
    • GORM
    • Hasura
      • Application Development
      • Benchmarking
    • JanusGraph
    • KairosDB
    • Liquibase
    • Metabase
    • Presto
    • Prisma
    • Sequelize
    • Spring Framework
      • Spring Data YugabyteDB
      • Spring Data JPA
      • Spring Data Cassandra
    • SQLAlchemy
    • WSO2 Identity Server
    • YSQL Loader
    • YugabyteDB JDBC driver
  • REFERENCE
    • Architecture
      • Design goals
      • Key concepts
        • Universe
        • YB-TServer Service
        • YB-Master Service
      • Core functions
        • Universe creation
        • Table creation
        • Write IO path
        • Read IO path
        • High availability
      • Layered architecture
      • Query layer
        • Overview
      • DocDB transactions layer
        • Transactions overview
        • Transaction isolation levels
        • Explicit locking
        • Read Committed
        • Single-row transactions
        • Distributed transactions
        • Transactional IO path
      • DocDB sharding layer
        • Hash & range sharding
        • Tablet splitting
        • Colocated tables
      • DocDB replication layer
        • Replication
        • xCluster replication
        • Read replicas
        • Change data capture (CDC)
      • DocDB storage layer
        • Persistence
        • Performance
    • APIs
      • YSQL
        • The SQL language
          • SQL statements
            • ABORT
            • ALTER DATABASE
            • ALTER DEFAULT PRIVILEGES
            • ALTER DOMAIN
            • ALTER FOREIGN DATA WRAPPER
            • ALTER FOREIGN TABLE
            • ALTER GROUP
            • ALTER POLICY
            • ALTER ROLE
            • ALTER SEQUENCE
            • ALTER SERVER
            • ALTER TABLE
            • ALTER USER
            • ANALYZE
            • BEGIN
            • CALL
            • COMMENT
            • COMMIT
            • COPY
            • CREATE AGGREGATE
            • CREATE CAST
            • CREATE DATABASE
            • CREATE DOMAIN
            • CREATE EXTENSION
            • CREATE FOREIGN DATA WRAPPER
            • CREATE FOREIGN TABLE
            • CREATE FUNCTION
            • CREATE GROUP
            • CREATE INDEX
            • CREATE MATERIALIZED VIEW
            • CREATE OPERATOR
            • CREATE OPERATOR CLASS
            • CREATE POLICY
            • CREATE PROCEDURE
            • CREATE ROLE
            • CREATE RULE
            • CREATE SCHEMA
            • CREATE SEQUENCE
            • CREATE SERVER
            • CREATE TABLE
            • CREATE TABLE AS
            • CREATE TRIGGER
            • CREATE TYPE
            • CREATE USER
            • CREATE USER MAPPING
            • CREATE VIEW
            • DEALLOCATE
            • DELETE
            • DO
            • DROP AGGREGATE
            • DROP CAST
            • DROP DATABASE
            • DROP DOMAIN
            • DROP EXTENSION
            • DROP FOREIGN DATA WRAPPER
            • DROP FOREIGN TABLE
            • DROP FUNCTION
            • DROP GROUP
            • DROP MATERIALIZED VIEW
            • DROP OPERATOR
            • DROP OPERATOR CLASS
            • DROP OWNED
            • DROP POLICY
            • DROP PROCEDURE
            • DROP ROLE
            • DROP RULE
            • DROP SEQUENCE
            • DROP SERVER
            • DROP TABLE
            • DROP TRIGGER
            • DROP TYPE
            • DROP USER
            • END
            • EXECUTE
            • EXPLAIN
            • GRANT
            • IMPORT FOREIGN SCHEMA
            • INSERT
            • LOCK
            • PREPARE
            • REASSIGN OWNED
            • REFRESH MATERIALIZED VIEW
            • RELEASE SAVEPOINT
            • RESET
            • REVOKE
            • ROLLBACK
            • ROLLBACK TO SAVEPOINT
            • SAVEPOINT
            • SELECT
            • SET
            • SET CONSTRAINTS
            • SET ROLE
            • SET SESSION AUTHORIZATION
            • SET TRANSACTION
            • SHOW
            • SHOW TRANSACTION
            • TRUNCATE
            • UPDATE
            • VALUES
          • WITH clause
            • WITH clause—SQL syntax and semantics
            • recursive CTE
            • case study—traversing an employee hierarchy
            • traversing general graphs
              • graph representation
              • common code
              • undirected cyclic graph
              • directed cyclic graph
              • directed acyclic graph
              • rooted tree
              • Unique containing paths
              • Stress testing find_paths()
            • case study—Bacon Numbers from IMDb
              • Bacon numbers for synthetic data
              • Bacon numbers for IMDb data
        • Data types
          • Array
            • array[] constructor
            • Literals
              • Text typecasting and literals
              • Array of primitive values
              • Row
              • Array of rows
            • FOREACH loop (PL/pgSQL)
            • array of DOMAINs
            • Functions and operators
              • ANY and ALL
              • Array comparison
              • Array slice operator
              • Array concatenation
              • Array properties
              • array_agg(), unnest(), generate_subscripts()
              • array_fill()
              • array_position(), array_positions()
              • array_remove()
              • array_replace() / set value
              • array_to_string()
              • string_to_array()
          • Binary
          • Boolean
          • Character
          • Date and time
            • Conceptual background
            • Timezones and UTC offsets
              • Catalog views
              • Extended_timezone_names
                • Unrestricted full projection
                • Real timezones with DST
                • Real timezones no DST
                • Synthetic timezones no DST
              • Offset/timezone-sensitive operations
                • Timestamptz to/from timestamp conversion
                • Pure 'day' interval arithmetic
              • Four ways to specify offset
                • Name-resolution rules
                  • 1 case-insensitive resolution
                  • 2 ~names.abbrev never searched
                  • 3 'set timezone' string not resolved in ~abbrevs.abbrev
                  • 4 ~abbrevs.abbrev before ~names.name
                  • Helper functions
              • Syntax contexts for offset
              • Recommended practice
            • Typecasting between date-time and text-values
            • Semantics of the date-time data types
              • Date data type
              • Time data type
              • Plain timestamp and timestamptz
              • Interval data type
                • Interval representation
                  • Ad hoc examples
                  • Representation model
                • Interval value limits
                • Declaring intervals
                • Justify() and extract(epoch...)
                • Interval arithmetic
                  • Interval-interval comparison
                  • Interval-interval addition and subtraction
                  • Interval-number multiplication
                  • Moment-moment overloads of "-"
                  • Moment-interval overloads of "+" and "-"
                • Custom interval domains
                • Interval utility functions
            • Typecasting between date-time datatypes
            • Operators
              • Test comparison overloads
              • Test addition overloads
              • Test subtraction overloads
              • Test multiplication overloads
              • Test division overloads
            • General-purpose functions
              • Creating date-time values
              • Manipulating date-time values
              • Current date-time moment
              • Delaying execution
              • Miscellaneous
                • Function age()
                • Function extract() | date_part()
                • Implementations that model the overlaps operator
            • Formatting functions
            • Case study—SQL stopwatch
            • Download & install the date-time utilities
            • ToC
          • JSON
            • JSON literals
            • Primitive and compound data types
            • Code example conventions
            • Indexes and check constraints
            • Functions & operators
              • ::jsonb, ::json, ::text (typecast)
              • ->, ->>, #>, #>> (JSON subvalues)
              • - and #- (remove)
              • || (concatenation)
              • = (equality)
              • @> and <@ (containment)
              • ? and ?| and ?& (key or value existence)
              • array_to_json()
              • jsonb_agg()
              • jsonb_array_elements()
              • jsonb_array_elements_text()
              • jsonb_array_length()
              • jsonb_build_object()
              • jsonb_build_array()
              • jsonb_each()
              • jsonb_each_text()
              • jsonb_extract_path()
              • jsonb_extract_path_text() and json_extract_path_text()
              • jsonb_object()
              • jsonb_object_agg()
              • jsonb_object_keys()
              • jsonb_populate_record()
              • jsonb_populate_recordset()
              • jsonb_pretty()
              • jsonb_set() and jsonb_insert()
              • jsonb_strip_nulls()
              • jsonb_to_record()
              • jsonb_to_recordset()
              • jsonb_typeof()
              • row_to_json()
              • to_jsonb()
          • Money
          • Numeric
          • Range
          • Serial
          • UUID
        • Functions and operators
          • Aggregate functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • grouping sets, rollup, cube
            • Per function signature and purpose
              • avg(), count(), max(), min(), sum()
              • array_agg(), string_agg(), jsonb_agg(), jsonb_object_agg()
              • bit_and(), bit_or(), bool_and(), bool_or()
              • variance(), var_pop(), var_samp(), stddev(), stddev_pop(), stddev_samp()
              • linear regression
                • covar_pop(), covar_samp(), corr()
                • regr_%()
              • mode(), percentile_disc(), percentile_cont()
              • rank(), dense_rank(), percent_rank(), cume_dist()
            • case study—percentile_cont() and the "68–95–99.7" rule
            • case study—linear regression on COVID data
              • Download the COVIDcast data
              • Ingest the COVIDcast data
                • Inspect the COVIDcast data
                • Copy the .csv files to staging tables
                • Check staged data conforms to the rules
                • Join the staged data into a single table
                • SQL scripts
                  • Create cr_staging_tables()
                  • Create cr_copy_from_scripts()
                  • Create assert_assumptions_ok()
                  • Create xform_to_covidcast_fb_survey_results()
                  • ingest-the-data.sql
              • Analyze the COVIDcast data
                • symptoms vs mask-wearing by day
                • Data for scatter-plot for 21-Oct-2020
                • Scatter-plot for 21-Oct-2020
                • SQL scripts
                  • analysis-queries.sql
                  • synthetic-data.sql
          • currval()
          • lastval()
          • nextval()
          • setval()
          • Window functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • Per function signature and purpose
              • row_number(), rank() and dense_rank()
              • percent_rank(), cume_dist() and ntile()
              • first_value(), nth_value(), last_value()
              • lag(), lead()
              • Tables for the code examples
                • table t1
                • table t2
                • table t3
                • table t4
            • case study—analyzing a normal distribution
              • Bucket allocation scheme
              • do_clean_start.sql
              • cr_show_t4.sql
              • cr_dp_views.sql
              • cr_int_views.sql
              • cr_pr_cd_equality_report.sql
              • cr_bucket_using_width_bucket.sql
              • cr_bucket_dedicated_code.sql
              • do_assert_bucket_ok
              • cr_histogram.sql
              • cr_do_ntile.sql
              • cr_do_percent_rank.sql
              • cr_do_cume_dist.sql
              • do_populate_results.sql
              • do_report_results.sql
              • do_compare_dp_results.sql
              • do_demo.sql
              • Reports
                • Histogram report
                • dp-results
                • compare-dp-results
                • int-results
          • yb_hash_code()
        • Keywords
        • Reserved names
      • YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • EXPLAIN
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple expressions
        • Subscripted expressions
        • Function call
        • Operators
        • BLOB
        • BOOLEAN
        • Collection
        • FROZEN
        • INET
        • Integer and counter
        • Non-integer
        • TEXT
        • DATE, TIME, and TIMESTAMP
        • UUID and TIMEUUID
        • JSONB
        • Date and time
        • BATCH
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • ysqlsh
      • ycqlsh
      • yb-admin
      • yb-ts-cli
      • ysql_dump
      • ysql_dumpall
    • Configuration
      • yb-tserver
      • yb-master
      • yugabyted
      • Default ports
    • Drivers and ORMs
      • JDBC Drivers
      • C# Drivers
      • Go Drivers
      • Python Drivers
      • Client drivers for YSQL
      • Client drivers for YCQL
    • Connectors
      • Kafka Connect YugabyteDB
    • Third party tools
      • pgAdmin
      • Apache Superset
      • Arctype
      • DBeaver
      • TablePlus
      • DbSchema
      • SQL Workbench/J
      • Cassandra Workbench
    • Sample datasets
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
      • Retail Analytics
  • RELEASES
    • Releases overview
      • v2.15 series (preview)
      • v2.13 series
      • v2.12 series (stable)
      • v2.11 series
      • v2.9 series
      • v2.8 series
      • v2.7 series
      • v2.6 series
      • v2.5 series
      • v2.4 series
      • v2.3 series
      • v2.2 series
      • v2.1 series
      • v2.0 series
      • v1.3 series
      • v1.2 series
    • Release versioning
  • FAQ
    • Comparisons
      • Amazon Aurora
      • Google Cloud Spanner
      • CockroachDB
      • TiDB
      • Vitess
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • PostgreSQL
      • Redis in-memory store
      • Apache HBase
    • General FAQ
    • Operations FAQ
    • API compatibility FAQ
    • YugabyteDB Anywhere FAQ
  • MISC
    • YEDIS
      • Quick start
      • Develop
        • Build an application
        • C#
        • C++
        • Go
        • Java
        • NodeJS
        • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
    • Legal
      • Third party software
> APIs > YSQL > Data types > Date and time > Semantics of the date-time data types > Interval data type >

User-defined interval utility functions

Report a doc issue Suggest new content Contributor guide
  • Generic helper function to test for the equality of two double precision values
    • function approx_equals (double precision, double precision) returns boolean
  • The interval utility functions
    • type interval_parameterization_t as (yy, mm, dd, hh, mi, ss)
    • function interval_parameterization (yy, mm, dd, hh, mi, ss) returns interval_parameterization_t
    • function interval_value (interval_parameterization_t) returns interval
    • function parameterization (interval) returns interval_parameterization_t
    • function approx_equals (p1_in in interval_parameterization_t, p2_in in interval_parameterization_t) returns boolean
    • type interval_mm_dd_ss_t as (mm, dd, ss)
    • function interval_mm_dd_ss (interval) returns interval_mm_dd_ss_t
    • function approx_equals (interval_mm_dd_ss_t, interval_mm_dd_ss_t) returns boolean
    • function interval_value (interval_mm_dd_ss_t) returns interval
    • function parameterization (interval_mm_dd_ss_t) returns interval_parameterization_t
    • function justified_seconds (interval) returns double precision
    • The user-defined "strict equals" interval-interval "==" operator
    • function to_timestamp_without_tz (double precision) returns timestamp
    • function to_time (double precision) returns time
  • Bonus functions
    • interval_mm_dd_ss_as_text(interval)
    • parameterization_as_text(interval)
    • parameterization_as_text(interval_mm_dd_ss_t)

Download the '.zip' file to create the reusable code that supports the pedagogy of the overall 'date-time' major section.

Download and install the code as the instructions here explain. The code that this page presents is included in the kit.

The code presented on this page defines two user-defined types, interval_parameterization_t and interval_mm_dd_ss_t. They model, respectively, the conventional parameterization of an interval value as a [yy, mm, dd, hh, mi, ss] tuple and the internal representation of an interval value as a [mm, dd, ss] tuple. Together with the interval data type itself, this makes three types—and therefore three possible pairs and six mutual transformation functions. Here is the summary:

┌———————————————————————————————————————————————————————————————————┬————————————————————————————————————————————————┐
│ type interval_parameterization_t as(                              │ type interval_mm_dd_ss_t as(                   │
│   yy double precision, mm double precision, dd double precision,  │   mm int, dd int, ss double precision(1000,6)) │
│   hh double precision, mi double precision, ss double precision)  │                                                │
└———————————————————————————————————————————————————————————————————┴————————————————————————————————————————————————┘

┌—————————————————————————————————————————————————┬—————————————————————————————————————————————————————┐
│ interval <-> interval_parameterization_t        │ interval_mm_dd_ss_t <-> interval_parameterization_t │
├—————————————————————————————————————————————————┼—————————————————————————————————————————————————————┤
│ function interval_value(                        │ function interval_mm_dd_ss(                         │
│  p in interval_parameterization_t               │   p in interval_parameterization_t)                 │
│  returns interval                               │   returns interval_mm_dd_ss_t                       │
├—————————————————————————————————————————————————┼—————————————————————————————————————————————————————┤
│  function parameterization(                     │  function parameterization(                         │
│    i in interval)                               │    i in interval_mm_dd_ss_t)                        │
│    returns interval_parameterization_t          │    returns interval_parameterization_t              │
└—————————————————————————————————————————————————┴—————————————————————————————————————————————————————┘

┌————————————————————————————─———————————————————————————————┐
│             interval <─> interval_mm_dd_ss_t               │
├————————————————————————————┬———————————————————————————————┤
│ function interval_value(   │ function interval_mm_dd_ss(   │
│  i in interval_mm_dd_ss_t) │   i in interval)              │
│  returns interval          │   returns interval_mm_dd_ss_t │
└————————————————————————————┴———————————————————————————————┘

The code that the remainder of this section presents defines the interval_parameterization_t and interval_mm_dd_ss_t types, and all but one of the six mutual transformation functions. The design of the five functions that are shown here is straightforward—but it does depend on knowing that the internal representation of an interval value is a [mm, dd, ss] tuple that uses four-byte integers to record the months and days fields and an eight-byte integer to record the seconds field as a microseconds value.

The section Modeling the internal representation and comparing the model with the actual implementation defines and tests the function that the present page doesn't show: function interval_mm_dd_ss(interval_parameterization_t). It implements, using PL/pgSQL, the rather complex algorithm that PostgreSQL, and therefore YSQL, use in their C code to transform a value, presented as a text literal, to the internal interval representation.

Here's an example of such a text literal:

'
     -9.123456 years,
     18.123456 months,
  -700.123456 days,
     97.123456 hours,
    -86.123456 minutes,
     75.123456 seconds
'

Generic helper function to test for the equality of two double precision values

PostgreSQL defines two fundamentally different data type families for representing real numbers and for supporting arithmetic using their values. Of course, YSQL inherits this regime.

The PostgreSQL documentation uses the terms Arbitrary Precision Numbers and Floating-Point Numbers for these two kinds of data type. The first kind is exemplified by the data type numeric. And the second kind is exemplified by double precision. There are other variants in each class. But the entire date_time section uses only these two (and uses only the unconstraint numeric variant). Moreover, it uses numeric only under duress. For example, you can easily round a numeric value to some desired number of decimal places like this:

select round(12.3456789::numeric, 3::int);

It produces the result 12.346. Try the corresponding operation on a double precision value:

with c as(
  select 12.3456789::double precision as v)
select round(v, 3::int) from c;

It causes the 42883 error:

function round(double precision, integer) does not exist

And the hint tells you that you might need to do some typecasting, like this:

with c as(
  select 12.3456789::double precision as v)
select round(v::numeric, 3::int)::double precision from c;

You'll see some examples of typecasting like this throughout the overall date-time major section.

Here, briefly, is how the two kinds of real number representation differ:

  • The numeric data type supports values that have a vast number of significant digits and very high accuracy. This is achieved by using an explicit software representation that differs from anything that underlying hardware might use. As a consequence, arithmetic that uses this representation has to be done using algorithms implemented ordinarily in software—in the C code of the PostgreSQL implementation that YSQL inherits "as is".
  • The double precision data type is essentially a direct exposure of the hardware representation and the corresponding hardware implementation of arithmetic that is specified by IEEE Standard 754 for Binary Floating-Point Arithmetic for double precision values. (The native scheme is augmented in software to handle nulls.)

You can guess that, in general, the representation of a double precision value uses less space than that of a numeric value and that double precision arithmetic is faster than numeric arithmetic. This is probably why many SQL built-in functions, and certainly those that are relevant for the date-time data types, use double precision to deal with real number values. Here are four examples:

  • The secs input formal parameter for make_interval() is double precision.
  • The sec input formal parameter for make_timestamp() and make_timestamptz() is double precision.
  • The anonymous input formal parameter for the single-parameter overload of to_timestamp() is double precision.
  • All of the functions in the extract() family return a double precision value.

For this reason, the user-defined interval utility functions that this page presents use double precision, and never numeric, for real number values.

Notice that equality comparison, using the native = operator, between real number values does bring a risk of evaluating to false when you expect it to be true because, of course, of the effect of rounding errors.

  • You can often use a simple equality test to confirm that two numeric values that you reason ought to be the same are indeed this. This is due to the data type's capacity for enormous precision and accuracy.
  • The corresponding test that uses the double precision overload for = for a pair of putatively identical values will fail more often because the data type has a smaller capacity for precision and a corresponding intrinsic inaccuracy.

However, you must accept that the native equality test, for values of both the numeric and the double precision data types, is unreliable. There's no native solution for this dilemma. But it's easy to implement your own operator. This is what function approx_equals(double precision, double precision) does. You can easily use it as a model for a (numeric, numeric) overload should you need this.

function approx_equals (double precision, double precision) returns boolean

In the context of the date-time data types, double precision values always represent seconds. And the internal representations record these only with microsecond precision. It's therefore good enough to test for equality using a 0.1 microsecond tolerance. Create the function thus:

drop function if exists approx_equals(double precision, double precision) cascade;

create function approx_equals(v1 in double precision, v2 in double precision)
  returns boolean
  language plpgsql
as $body$
declare
  microseconds_diff       constant double precision not null := abs(v1 - v2);
  point_one_microseconds  constant double precision not null := 0.0000001;
  eq                      constant boolean          not null := microseconds_diff < point_one_microseconds;
begin
  return eq;
end;
$body$;

And map it to the ~= operator thus:

drop operator if exists ~= (double precision, double precision) cascade;

create operator ~= (
  leftarg   = double precision,
  rightarg  = double precision,
  procedure = approx_equals);

Here's a simple test:

with
  c1 as(
    select 10::double precision as base, 234.567::double precision as v),

  c2 as(
    select base, v, log(v) as log_v from c1),

  c3 as(
    select v as orig, base^log_v as recovered from c2)

select
  to_char(orig,      '9999.999999999999999')  as orig,
  to_char(recovered, '9999.999999999999999')  as recovered,
  (recovered =  orig)::text                   as native_equals,
  (recovered ~= orig)::text                   as approx_equals
from c3;

This is the result:

        orig        |     recovered      | native_equals | approx_equals
--------------------+--------------------+---------------+---------------
   234.567000000000 |   234.567000000000 | false         | true

It's rather strange that the to_char() renditions of the internally represented double precision values that the native = operator shows to be different are indistinguishable. Notice that the to_char() format mask asks for fifteen decimal digits but only twelve are rendered. This is a feature of to_char(): if you ask to see more decimal digits than are supported, then you silently see the maximum supported number. The outcome of this test only serves to emphasize the importance of creating your own user-defined equality operator for the comparison of real number values.

The interval utility functions

type interval_parameterization_t as (yy, mm, dd, hh, mi, ss)

When you define an interval value using either the ::interval typecast of a text literal or the make_interval() built-in function, you specify values for some or all of years, months, days, hours, minutes, and seconds. Try this, using the ::interval typecast approach:

select
  '5 years 4 months'              ::interval as i1,
  '40 days'                       ::interval as i2,
  '9 hours 30 minutes 45 seconds' ::interval as i3;

This is the result:

       i1       |   i2    |    i3
----------------+---------+----------
 5 years 4 mons | 40 days | 09:30:45

And try this, using the make_interval() approach:

select
  make_interval(years=>5, months=>4)          as i1,
  make_interval(days=>40)                     as i2,
  make_interval(hours=>9, mins=>30, secs=>45) as i3;

It produces the same result as using the ::interval typecast approach. Notice that the ::interval typecast approach allows real number values for each of the six parameters. In contrast, the make_interval() function defines all of its formal parameters except for secs with the data type integer, and It defines secs with the data type double precision.

Each approach allows other parameters to specify, for example, centuries, weeks, or microseconds. But the six parameters shown above are the most-commonly used. They are jointly more than sufficient to define non-zero values for each of the three fields of the internal representation. The bare minimum that gets an obviously predictable result is just months, days, and seconds. (See the section How does YSQL represent an interval value?.)

Omitting one of the parameters has the same effect as specifying zero for it. Create the type interval_parameterization_t to represent this six-field tuple.

drop type if exists interval_parameterization_t cascade;

create type interval_parameterization_t as(
  yy double precision,
  mm double precision,
  dd double precision,
  hh double precision,
  mi double precision,
  ss double precision);

Other functions, below, have a single formal parameter with data type interval_parameterization_t or are defined to return an instance of that type.

function interval_parameterization (yy, mm, dd, hh, mi, ss) returns interval_parameterization_t

You can't define default values for the fields of a user-defined type. To avoid verbose code when you want to specify non-zero values for only some, or especially just one, of the six fields, the helper function interval_parameterization() is defined with default values for its corresponding six formal parameters.

drop function if exists interval_parameterization(
  double precision, double precision, double precision, double precision, double precision, double precision);

create function interval_parameterization(
  yy in double precision default 0,
  mm in double precision default 0,
  dd in double precision default 0,
  hh in double precision default 0,
  mi in double precision default 0,
  ss in double precision default 0)
  returns interval_parameterization_t
  language plpgsql
as $body$
declare
  ok constant boolean :=
    (yy is not null) and
    (mm is not null) and
    (dd is not null) and
    (hh is not null) and
    (mi is not null) and
    (ss is not null);
  p interval_parameterization_t not null :=
   (yy, mm, dd, hh, mi, ss)::interval_parameterization_t;
begin
  assert ok, 'No argument, when provided, may be null';
  return p;
end;
$body$;

Test it like this:

select interval_parameterization(yy=>5, mm=>6)::text;

This is the result:

 interval_parameterization
---------------------------
 (5,6,0,0,0,0)

function interval_value (interval_parameterization_t) returns interval

This function constructs an interval value from an interval_parameterization_t instance. Create it thus:

drop function if exists interval_value(interval_parameterization_t) cascade;

create function interval_value(p in interval_parameterization_t)
  returns interval
  language plpgsql
as $body$
declare
  yy constant interval not null := p.yy::text ||' years';
  mm constant interval not null := p.mm::text ||' months';
  dd constant interval not null := p.dd::text ||' days';
  hh constant interval not null := p.hh::text ||' hours';
  mi constant interval not null := p.mi::text ||' minutes';
  ss constant interval not null := p.ss::text ||' seconds';
begin
  return yy + mm + dd + hh + mi + ss;
end;
$body$;

It uses the ::interval typecast approach rather than the make_interval() approach because the pedagogy of the section How does YSQL represent an interval value? depends on being able to specify real number values for each of the six parameters.

Test it like this:

select interval_value(interval_parameterization(mm=>2.345, dd=>3.456))::text;

This is the result:

      interval_value
---------------------------
 2 mons 13 days 19:20:38.4

function parameterization (interval) returns interval_parameterization_t

Create the function thus:

drop function if exists parameterization(interval) cascade;

create function parameterization(i in interval)
  returns interval_parameterization_t
  language plpgsql
as $body$
declare
  -- All but the seconds value are always integral.
  yy  double precision not null := round(extract(years   from i));
  mm  double precision not null := round(extract(months  from i));
  dd  double precision not null := round(extract(days    from i));
  hh  double precision not null := round(extract(hours   from i));
  mi  double precision not null := round(extract(minutes from i));
  ss  double precision not null :=       extract(seconds from i);
begin
  return (yy, mm, dd, hh, mi, ss)::interval_parameterization_t;
end;
$body$;

Test it like this:

select parameterization('2 months 13 days 19:20:38.4'::interval)::text;

This is the result:

 parameterization
--------------------------------------
 (0,2,13,19,20,38.4)

function approx_equals (p1_in in interval_parameterization_t, p2_in in interval_parameterization_t) returns boolean

Create a function to test a pair of "interval_parameterization_t values" for equality. The function parameterization(i in interval) uses extract() and this accesses the internal [mm, dd, ss] representation. There's a risk of rounding errors here. For example, when the ss field corresponds 04:48:00, this might be extracted as 04:47,59.99999999.... The approx_equals() implementation needs to accommodate this.

drop function if exists approx_equals(interval_parameterization_t, interval_parameterization_t) cascade;

create function approx_equals(p1_in in interval_parameterization_t, p2_in in interval_parameterization_t)
  returns boolean
  language plpgsql
as $body$
declare
  -- There's no need (for the present pedagogical purpose) to extend this to
  -- handle NULL inputs. It would be simple to do this.
  p1    constant interval_parameterization_t not null := p1_in;
  p2    constant interval_parameterization_t not null := p2_in;

  mons1 constant double precision            not null := p1.yy*12.0 + p1.mm;
  mons2 constant double precision            not null := p2.yy*12.0 + p2.mm;

  secs1 constant double precision            not null := p1.hh*60.0*60.0 + p1.mi*60.0 + p1.ss;
  secs2 constant double precision            not null := p2.hh*60.0*60.0 + p2.mi*60.0 + p2.ss;

  eq    constant boolean                     not null := (mons1 ~= mons2) and
                                                         (p1.dd ~= p2.dd) and
                                                         (secs1 ~= secs2);
begin
  return eq;
end;
$body$;

And map it to the ~= operator thus:

drop operator if exists ~= (interval_parameterization_t, interval_parameterization_t) cascade;

create operator ~= (
  leftarg   = interval_parameterization_t,
  rightarg  = interval_parameterization_t,
  procedure = approx_equals);

type interval_mm_dd_ss_t as (mm, dd, ss)

The type interval_mm_dd_ss_t models the internal representation of an interval value. It's central to the pedagogy of the sections How does YSQL represent an interval value? and Interval arithmetic. It is also used to implement the user-defined domains described in the section Custom domain types for specializing the native interval functionality.

drop type if exists interval_mm_dd_ss_t cascade;

create type interval_mm_dd_ss_t as(
  mm int, dd int, ss double precision);

function interval_mm_dd_ss (interval) returns interval_mm_dd_ss_t

Create a function to create an interval_mm_dd_ss_t value from an interval value:

drop function if exists interval_mm_dd_ss(interval) cascade;

create function interval_mm_dd_ss(i in interval)
  returns interval_mm_dd_ss_t
  language plpgsql
as $body$
begin
  if i is null then
    return null;
  else
    declare
      mm  constant int              not null := (extract(years from i))*12 +
                                                 extract(months from i);

      dd  constant int              not null := extract(days from i);

      ss  constant double precision not null := (extract(hours   from i))*60*60 +
                                                 extract(minutes from i)*60 +
                                                 extract(seconds from i);
    begin
      return (mm, dd, ss);
    end;
  end if;
end;
$body$;

Test it like this:

select interval_mm_dd_ss('2.345 months 3.456 days 19:20:38.423456'::interval)::text;

This is the result:

 (2,13,139276.823456)

function approx_equals (interval_mm_dd_ss_t, interval_mm_dd_ss_t) returns boolean

The seconds field of the [mm, dd, ss] tuple that defines type interval_mm_dd_ss_t is declared as double precision. This means that the native = test on a pair of values of this type (it uses a naïve field-by-field equality comparison) will be subject to the underlying challenge of testing for the equality of two double precision values. This implies the need for a user-defined approximate comparison operator for interval_mm_dd_ss_t values.

Create the function thus using the user-defined ~= operator for double precision values:

drop function if exists approx_equals(interval_mm_dd_ss_t, interval_mm_dd_ss_t) cascade;

create function approx_equals(i1_in in interval_mm_dd_ss_t, i2_in in interval_mm_dd_ss_t)
  returns boolean
  language plpgsql
as $body$
declare
  -- There's no need (for the present pedagogical purpose) to extend this to
  -- handle NULL inputs. It would be simple to do this.
  i1 constant interval_mm_dd_ss_t not null := i1_in;
  i2 constant interval_mm_dd_ss_t not null := i2_in;
  eq constant boolean             not null := (i1.mm =  i2.mm) and
                                              (i1.dd =  i2.dd) and
                                              (i1.ss ~= i2.ss);
begin
  return eq;
end;
$body$;

And map it to the ~= operator thus:

drop operator if exists ~= (interval_mm_dd_ss_t, interval_mm_dd_ss_t) cascade;

create operator ~= (
  leftarg   = interval_mm_dd_ss_t,
  rightarg  = interval_mm_dd_ss_t,
  procedure = approx_equals);

function interval_value (interval_mm_dd_ss_t) returns interval

Create a function to create an interval value from an interval_mm_dd_ss_t instance:

drop function if exists interval_value(interval_mm_dd_ss_t) cascade;

create function interval_value(i in interval_mm_dd_ss_t)
  returns interval
  language plpgsql
as $body$
begin
  return make_interval(months=>i.mm, days=>i.dd, secs=>i.ss);
end;
$body$;

Test it like this:

select interval_value((2,13,139276.823456)::interval_mm_dd_ss_t)::text;

This is the result:

 2 mons 13 days 38:41:16.823456

function parameterization (interval_mm_dd_ss_t) returns interval_parameterization_t

Note: The implementation of the function parameterization(interval_mm_dd_ss_t) documents the algorithm for calculating the text typecast of an interval value (or equivalently the result of using the extract functions ).

Here is an example of using extract:

select extract(hours from '123 months 234 days 34567.123456 seconds'::interval) as "extracted hours";

This is the result:

 extracted hours
-----------------
               9

The extract functions are used to implement the function parameterization() shown above.

Create the function parameterization(interval_mm_dd_ss_t) thus:

drop function if exists parameterization(interval_mm_dd_ss_t) cascade;

create function parameterization(i in interval_mm_dd_ss_t)
  returns interval_parameterization_t
  language plpgsql
as $body$
declare
  yy  constant int              := trunc(i.mm/12);
  mm  constant int              := i.mm - yy*12;
  dd  constant int              := i.dd;
  hh  constant int              := trunc(i.ss/(60.0*60.0));
  mi  constant int              := trunc((i.ss - hh*60.0*60)/60.0);
  ss  constant double precision := i.ss - (hh*60.0*60.0 + mi*60.0);
begin
  return (yy, mm, dd, hh, mi, ss)::interval_parameterization_t;
end;
$body$;

Test it like this:

select parameterization((123, 234, 34567.123456)::interval_mm_dd_ss_t)::text;

This is the result:

 (10,3,234,9,36,7.12345600000117)

Notice the apparent inaccuracy brought by the use of double precision. This is inevitable. Compare the result with that from using parameterization(interval) on an actual interval value:

select parameterization('123 months 234 days 34567.123456 seconds'::interval)::text;

This is the result:

  (10,3,234,9,36,7.123456)

This emphasizes the need for the user-defined ~= operator for interval_parameterization_t values. Do this:

select (
    parameterization((123, 234, 34567.123456)::interval_mm_dd_ss_t) ~=
    parameterization('123 months 234 days 34567.123456 seconds'::interval)
  )::text;

The result is true.

function justified_seconds (interval) returns double precision

This function is discussed in the section The justify() and extract(epoch ...) functions for interval values. And the section Comparing two interval values relies on this function to model the implementation of the comparison algorithm.

The semantics of the justify_interval() built-in function (explained here in the section The justify() and extract(epoch ...) functions for interval values suggests a scheme to map an interval value (a vector with three components) to a real number. The same rule of thumb that justify_interval() uses to normalize the ss and the dd fields of the internal representation (24 hours is deemed to be the same as 1 day and 30 days is deemed to be the same as 1 month) can be used to compute a number of seconds from an interval value.

You probably won't use this function in application code. But it's used in the section Comparing two interval values to model the implementation of the comparison algorithm. It also allows you to understand how it's possible to use an order by predicate with an interval table column (like, for example, pg_timezone_names.utc_offset) and to create an index on such a column.

Create the justified_seconds() thus:

drop function if exists justified_seconds(interval) cascade;

create function justified_seconds(i in interval)
  returns double precision
  language plpgsql
as $body$
begin
  if i is null then
    return null;
  else
    declare
      secs_pr_day    constant double precision    not null := 24*60*60;
      secs_pr_month  constant double precision    not null := secs_pr_day*30;

      r              constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
      ss             constant double precision    not null := r.ss + r.dd*secs_pr_day + r.mm*secs_pr_month;

      rj             constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(justify_interval(i));
      ssj            constant double precision    not null := rj.ss + rj.dd*secs_pr_day + rj.mm*secs_pr_month;
    begin
      assert ss = ssj, 'justified_seconds(): assert failed';
      return ss;
    end;
  end if;
end;
$body$;

Notice the assert statement that tests that the computed number of seconds from an "as is" interval value is identical to the computed number of seconds from an interval value that's normalized using justify_interval(). You might argue, correctly, that what the assert statement tests can be proved algebraically. The assert is used, here, just as a documentation device.

The subsection that describes this function in the section The justify() and extract(epoch ...) functions for interval values demonstrates its result for a selection of input values.

The user-defined "strict equals" interval-interval "==" operator

Use the 'strict equals' operator, '==', rather than the native '=', to compare 'interval' values.

Yugabyte staff members have carefully considered the practical value of the native interval-interval overload of the = operator that YSQL inherits from PostgreSQL.

They believe that the use-cases where the functionality will be useful are rare—and that, rather, a "strict equals" notion, that requires pairwise equality of the individual fields of the [mm, dd, ss] internal representations of the interval values that are compared, will generally be more valuable.

See the section Comparing two interval values for the larger discussion on this topic.

Create the strict_equals() function thus:

drop function if exists strict_equals(interval, interval) cascade;

create function strict_equals(i1 in interval, i2 in interval)
  returns boolean
  language plpgsql
as $body$
begin
  if i1 is null or i2 is null then
    return null;
  else
    declare
      mm_dd_ss_1 constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i1);
      mm_dd_ss_2 constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i2);
    begin
      return mm_dd_ss_1 ~= mm_dd_ss_2;
    end;
  end if;
end;
$body$;

And map it to the == operator thus:

drop operator if exists == (interval, interval) cascade;

create operator == (
  leftarg   = interval,
  rightarg  = interval,
  procedure = strict_equals);

function to_timestamp_without_tz (double precision) returns timestamp

Strangely, there is no native plain timestamp equivalent of the to_timestamp() function. But it's easy to write your own. Notice that a pair of functions cannot be overload-distinguished when they differ only in the return type. So your implementation of the missing functionality must have a new unique name.

drop function if exists to_timestamp_without_tz(double precision);

create function to_timestamp_without_tz(ss_from_epoch in double precision)
  returns /* plain */ timestamp
  language plpgsql
as $body$
declare
  current_tz text not null := current_setting('TimeZone');
begin
  assert length(current_tz) > 0, 'undefined time zone';
  set timezone = 'UTC';
  declare
    t_tz constant timestamptz := to_timestamp(ss_from_epoch);
    t    constant timestamp   := t_tz at time zone 'UTC';
  begin
    -- Restore the saved time zone setting.
    execute 'set timezone = '''||current_tz||'''';
    return t;
  end;
end;
$body$;

Test it like this:

select to_timestamp_without_tz(42123.456789);

This is the result:

 1970-01-01 11:42:03.456789

function to_time (double precision) returns time

Strangely, there is no native to_time() function to transform some number of seconds from midnight to a time value. But it's easy to write your own.

drop function if exists to_time(double precision) cascade;

-- mod() doesn't have an overload for "double precision" arguments.
create function to_time(ss in double precision)
  returns time
  language plpgsql
as $body$
declare
  -- Notice the ss value can be bigger than ss_per_day.
  ss_per_day        constant  numeric          not null := 24.0*60.0*60.0;
  ss_from_midnight  constant  double precision not null := mod(ss::numeric, ss_per_day);
  t                 constant  time             not null :=
                      make_interval(secs=>ss_from_midnight)::time;
begin
  return t;
end;
$body$;

Test it like this:

select to_time((29*60*60 + 17*60)::double precision + 42.123456::double precision);

This is the result:

 05:17:42.123456

Bonus functions

The three functions shown below aren't used anywhere else in the documentation of the interval data type. But they were used extensively for ad hoc experiments and as a tracing tool while the code examples that this overall interval section uses were being developed. You might find them useful for the same reason.

interval_mm_dd_ss_as_text(interval)

The function interval_mm_dd_ss_as_text(interval) returns the same information that the function interval_mm_dd_ss(interval) returns, but as a text literal for the interval value so that it can be used directly in any context that needs this. Create it thus:

drop function if exists interval_mm_dd_ss_as_text(interval) cascade;

create function interval_mm_dd_ss_as_text(i in interval)
  returns text
  language plpgsql
as $body$
declare
  mm_dd_ss constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
  ss_text  constant text                not null := ltrim(to_char(mm_dd_ss.ss, '9999999999990.999999'));
begin
  return
    mm_dd_ss.mm::text||' months ' ||
    mm_dd_ss.dd::text||' days '   ||
    ss_text          ||' seconds' ;
end;
$body$;

Test it like this:

select interval_mm_dd_ss_as_text('2 years 3 months 999 days 77 hours 53 min 17.123456 secs'::interval);

This is the result:

27 months 999 days 280397.123456 seconds

parameterization_as_text(interval)

The function parameterization_as_text(interval) returns the same information that the function parameterization(interval) returns, but as a text literal for the interval value so that it can be used directly in any context that needs this. Create it thus:

drop function if exists parameterization_as_text(interval) cascade;

create function parameterization_as_text(i in interval)
  returns text
  language plpgsql
as $body$
declare
  p        constant interval_parameterization_t not null := parameterization(i);
  ss_text  constant text                        not null := ltrim(to_char(p.ss, '9999999999990.999999'));
begin
  return
    p.yy::text||' years '   ||
    p.mm::text||' months '  ||
    p.dd::text||' days '    ||
    p.hh::text||' hours '   ||
    p.mi::text||' minutes ' ||
    ss_text   ||' seconds';
end;
$body$;

Test it like this:

select parameterization_as_text('42 months 999 days 77.12345 hours'::interval);

This is the result:

 3 years 6 months 999 days 77 hours 7 minutes 24.420000 seconds

parameterization_as_text(interval_mm_dd_ss_t)

Just as the function parameterization_as_text(interval) does for an interval value, it can be useful, too, to present the same information that the function parameterization(interval_mm_dd_ss_t) returns, again as a text literal for the interval value. The function parameterization_as_text(interval_mm_dd_ss_t) does this. Create it thus:

drop function if exists parameterization_as_text(interval_mm_dd_ss_t) cascade;

create function parameterization_as_text(i in interval_mm_dd_ss_t)
  returns text
  language plpgsql
as $body$
declare
  p        constant interval_parameterization_t not null := parameterization(i);
  ss_text  constant text                        not null := ltrim(to_char(p.ss, '9999999999990.999999'));
begin
  return
    p.yy::text||' years '   ||
    p.mm::text||' months '  ||
    p.dd::text||' days '    ||
    p.hh::text||' hours '   ||
    p.mi::text||' minutes ' ||
    ss_text   ||' seconds';
end;
$body$;

Test it like this:

select parameterization_as_text((77,13,139276.800000)::interval_mm_dd_ss_t)::text;

This is the result:

6 years 5 months 13 days 38 hours 41 minutes 16.800000 seconds

Compare this with the output of function parameterization_as_text(interval) for the same input:

select parameterization_as_text('77 months, 13 days, 139276.800000 secs'::interval )::text;

The result is identical.

All this vividly makes the point that a particular actual interval value can be created using many different variants of the corresponding text literal. Try this:

select
  (
    '2.345 months 3.456 days 19:20:38.4'::interval == '2 mons 13 days 38:41:16.8'::interval
    and
    '2.345 months 3.456 days 19:20:38.4'::interval == '2 months 13 days 139276.800000 seconds'::interval
  )
  ::text as "all the same";

This is the result:

 all the same
--------------
 true
  • Generic helper function to test for the equality of two double precision values
    • function approx_equals (double precision, double precision) returns boolean
  • The interval utility functions
    • type interval_parameterization_t as (yy, mm, dd, hh, mi, ss)
    • function interval_parameterization (yy, mm, dd, hh, mi, ss) returns interval_parameterization_t
    • function interval_value (interval_parameterization_t) returns interval
    • function parameterization (interval) returns interval_parameterization_t
    • function approx_equals (p1_in in interval_parameterization_t, p2_in in interval_parameterization_t) returns boolean
    • type interval_mm_dd_ss_t as (mm, dd, ss)
    • function interval_mm_dd_ss (interval) returns interval_mm_dd_ss_t
    • function approx_equals (interval_mm_dd_ss_t, interval_mm_dd_ss_t) returns boolean
    • function interval_value (interval_mm_dd_ss_t) returns interval
    • function parameterization (interval_mm_dd_ss_t) returns interval_parameterization_t
    • function justified_seconds (interval) returns double precision
    • The user-defined "strict equals" interval-interval "==" operator
    • function to_timestamp_without_tz (double precision) returns timestamp
    • function to_time (double precision) returns time
  • Bonus functions
    • interval_mm_dd_ss_as_text(interval)
    • parameterization_as_text(interval)
    • parameterization_as_text(interval_mm_dd_ss_t)
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2022 Yugabyte, Inc. All rights reserved.