Join us on YugabyteDB Community Slack
Star us on
Get Started
Slack
GitHub
Get Started
v2.13 (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)
        • Debezium connector
        • Java CDC console
      • Security
      • Observability
        • Prometheus Integration
        • Grafana Dashboard
    • Drivers and ORMs
      • Java
        • JDBC drivers
        • Hibernate ORM
        • Supported versions
      • Go
        • Go drivers
        • Go ORMs
        • Supported versions
      • C#
        • C# drivers
        • C# ORMs
        • Supported versions
      • 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
      • Cloud-native development
        • Codespaces
        • Gitpod
    • Migrate
      • Migration process overview
      • Migrate from PostgreSQL
        • 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
      • Back up and restore
        • Export and import data
        • Snapshot and restore data
        • 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
      • 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
          • 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
      • Universe issues
    • Administer
      • Back up YugabyteDB Anywhere
      • Authenticate with LDAP
    • Upgrade
      • Upgrade Kubernetes installation
      • Upgrade using Replicated
  • YUGABYTEDB MANAGED
    • Overview
    • Quick start
      • Create a free 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
      • Planning a cluster
      • Create a free cluster
      • Create a standard cluster
      • VPC network
        • Overview
        • VPCs
        • Peering connections
        • Create a VPC Network
    • 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 YSQL queries
      • Cluster activity
    • Manage clusters
      • Scale and configure clusters
      • Backup and restore
      • Maintenance windows
      • Create extensions
    • Administration and billing
      • Manage account access
      • 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
    • 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()
          • 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.13 series (preview)
      • 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 >

Declaring intervals

Report a doc issue Suggest new content Contributor guide
  • Summary
  • Interval declaration syntax variants
  • Syntax variants within each of just six groups have the same effect
  • The effect of the optional (p) element
  • Modeling the implementation

Download and [re]install the date-time utilities code.

The code on this page depends on the code presented in the section User-defined interval utility functions and on the function interval_mm_dd_ss (interval_parameterization_t), explained in the section Modeling the internal representation and comparing the model with the actual implementation. This is all included in the larger code kit that includes all of the reusable code that the overall date-time section describes and uses.

Even if you have already installed the code kit, install it again now. Do this because a code example in the section How does YSQL represent an interval value? redefines one of the interval utility functions.

There are over one hundred different spellings of the declaration of an interval. This might seem daunting. However, when you understand the degrees of freedom that the variations exploit, the mental model will seem straightforward. By analogy, when you consider the optional annotations of a bare numeric declaration to specify the scale and precision, you realize that multiplying the numbers of possible spellings for these two annotations gives a vast number of distinct possible spellings. But you need only to understand the concepts of scale and precision. The syntax variants for interval declarations express analogous concepts. This page explains it all. And it also points out that the variations in the syntax spellings can be grouped to reflect the fact that, within each group, the variants all express the same semantics.

However, if you follow the approach described in the section Custom domain types for specializing the native interval functionality, then you will not need to understand what this page explains.

Summary

The explanations and the supporting code below address the semantics of the different spellings of interval declarations—in other words, how these different spellings differently constrain the interval values that can be represented. The code and the explanations presented below show that though there are several syntax spellings for each, there are in fact just six kinds of interval declaration. It's convenient to call these kinds year, month, day, hour, minute, and second. The constraints are applied whenever a new interval value is created, just before recording it in the internal [mm, dd, ss] tuple format. The effects of each of the six constraints are conveniently described by this PL/pgSQL code:

-- Here with [mm, dd, ss] computed with, so far, no constraints.
case mode
  when 'bare' then
    null;
  when 'second' then
    null;
  when 'minute' then
    ss := trunc(ss/60.0)*60.0;
  when 'hour' then
    ss := trunc(ss/(60.0*60.0))*60.0*60.0;
  when 'day' then
    ss := 0.0;
  when 'month' then
    ss := 0.0;
    dd := 0;
  when 'year' then
    ss := 0.0;
    dd := 0;
    mm := (mm/12)*12; -- integer division uses "trunc()" semantics
end case;

Yugabyte recommends using only the bare 'interval' declaration.

The term of art "bare interval" is to be taken literally: no trailing key words, and no (p) precision specifier.

Yugabyte staff members have carefully considered the practical value that these various constraints bring and have concluded that none captures the intent of the SQL Standard or brings useful functionality. The section Custom domain types for specializing the native interval functionality shows how you can usefully constrain interval values, declared using the bare syntax, to allow the internal interval representation to record only:

  • either the years and months fields (corresponding to the mm field in the internal representation).
  • or the days field (corresponding to the dd field in the internal representation).
  • or the hours, minutes, and seconds fields (corresponding to the ss field in the internal representation).

If you follow this recommendation, then you don't need to study the remainder of this section.

The SQL Standard (cosmetically reworded) says this:

There are two classes of intervals. One class, called year-month intervals, has an express or implied date-time precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval value precision that includes no fields other than DAY, HOUR, MINUTE, and SECOND, though not all are required.

Briefly, day-time intervals implement clock-time-semantics and year-month intervals implement calendar-time-semantics. See the section Two ways of conceiving of time: calendar-time and clock-time.

The PostgreSQL design that YSQL inherits allows, for example, the declaration spelling day to second but, as the tests below show, the day to part of the phrase has no effect and so all six fields (years, months, days, hours, minutes , and seconds) are allowed. This is not useful—in particular because the sharp distinction that the SQL Standard intends between clock-time-semantics and calendar-time-semantics is blurred: hybrid semantics emerge.

However, the regime that the custom domains approach defines is useful because the semantics of interval arithmetic for the values of each of the constrained domains is different—and honors the spirit of the SQL Standard. See the section Interval arithmetic. Moreover, the fact that there are three variants, in contrast to the SQL Standard's two variants, brings beneficial extra functionality.

Interval declaration syntax variants

The PostgreSQL documentation specifies that this is the general syntax for an interval declaration:

interval [ fields ] [ (p) ]

And it states that the fields element restricts the set of stored fields by writing one of these listed phrases (or, as the syntax diagram says, by writing nothing):

            year

            month
year to     month

day

            hour
day to      hour

            minute
day to      minute
hour to     minute

            second
day to      second
hour to     second
minute to   second
<"fields" specification omitted>

This list shows no fewer than fourteen distinct syntax spellings.

The optional (p) element specifies the precision, in microseconds, with which the seconds field of the internal representation records the value. It is legal only after the "second" keyword or after the bare interval declaration. The allowed values are in 0..6. Omitting the (p) element has the same effect as specifying (6). So with the fourteen spellings listed above, together with the eight precision designations (0, 1, 2, 3, 4, 5, 6, or omitting this element) it multiplies up to 112 distinct ways to declare an interval as a table column, in PL/pgSQL code, and so on.

Note: No pair chosen from the fourteen different interval declarations can be used to distinguish procedure or function overloads. The same applies to the variations in the specification of the (p) element.

The account below shows that the different declaration syntaxes produce only six distinct semantic outcomes. The blank lines in the list above, and in the code, reflect this by grouping the syntaxes according to their semantic outcome.

Syntax variants within each of just six groups have the same effect

The anonymous block below demonstrates the syntax groupings. It uses integral values for each of the inputs, except for the seconds, to make the results maximally understandable. The expected outcomes, used on the right hand sides of the putative equalities that the asserts test, are consistent with the explanations that the section How does YSQL represent an interval value? gave of how a parameterization that specifies values using a [yy, mm, dd, hh, mi, ss] tuple is encoded as the internal representation that uses a [mm, dd, ss] tuple with integral mm and dd fields and a real number ss field.

do $body$
declare
  i_bare      constant  interval :=

    make_interval(years=>9, months=>18, days=>700, hours=>97, mins=>86, secs=>75.123456);

  i_year      constant  interval              year    := i_bare;

  i_month_1   constant  interval              month   := i_bare;
  i_month_2   constant  interval  year   to   month   := i_bare;

  i_day       constant  interval              day     := i_bare;

  i_hour_1    constant  interval              hour    := i_bare;
  i_hour_2    constant  interval  day     to  hour    := i_bare;

  i_minute_1  constant  interval              minute  := i_bare;
  i_minute_2  constant  interval  day     to  minute  := i_bare;
  i_minute_3  constant  interval  hour    to  minute  := i_bare;

  i_second_1  constant  interval              second  := i_bare;
  i_second_2  constant  interval  day     to  second  := i_bare;
  i_second_3  constant  interval  hour    to  second  := i_bare;
  i_second_4  constant  interval  minute  to  second  := i_bare;

  r_year    constant text := '10 years';
  r_month   constant text := '10 years 6 mons';
  r_day     constant text := '10 years 6 mons 700 days';
  r_hour    constant text := '10 years 6 mons 700 days 98:00:00';
  r_minute  constant text := '10 years 6 mons 700 days 98:27:00';
  r_second  constant text := '10 years 6 mons 700 days 98:27:15.123456';
begin
  -- "Year" group
  assert i_year::text = r_year, 'i_year = r_year failed';

  -- "Month" group
  assert i_month_1::text = r_month, 'i_month_1 = r_month failed';
  assert i_month_2::text = r_month, 'i_month_2 = r_month failed';

  -- "Day" group
  assert i_day::text = r_day, 'i_day = r_day failed';

  -- "Hour" group
  assert i_hour_1::text = r_hour, 'i_hour_1 = r_hour failed';
  assert i_hour_2::text = r_hour, 'i_hour_2 = r_hour failed';

  -- "Minute" group
  assert i_minute_1::text = r_minute, 'i_minute_1 = r_minute failed';
  assert i_minute_2::text = r_minute, 'i_minute_2 = r_minute failed';
  assert i_minute_3::text = r_minute, 'i_minute_3 = r_minute failed';

  -- "Second" group
  assert i_bare    ::text = r_second, 'i_bare     = r_second failed';
  assert i_second_1::text = r_second, 'i_second_1 = r_second failed';
  assert i_second_2::text = r_second, 'i_second_2 = r_second failed';
  assert i_second_3::text = r_second, 'i_second_3 = r_second failed';
  assert i_second_4::text = r_second, 'i_second_4 = r_second failed';
end;
$body$;

The block finishes silently, showing that each assertion holds.

The declarations are grouped according to the six possible choices for the trailing keyword: year, month, day, hour, minute, or second. The assertions show that the syntax variants within each group have the same effect—in other words that the optional leading phrases, year to, day to, hour to, and minute to have no semantic effect. Only the trailing keyword is semantically significant. Omitting this keyword (i.e. the bare declaration) has the same semantic effect has writing second, and so it belongs in that group.

Each group has different resolution semantics: the choice of trailing keyword determines the least granular unit (years, months, days, hours, minutes, or seconds) that is respected.

The effect of the optional (p) element

Try this:

do $body$
declare
  i_bare constant interval :=

    '9 years 18 months 700 days 97 hours 86 minutes 75.123456 seconds';

  i6 constant interval(6) not null := i_bare;
  i5 constant interval(5) not null := i_bare;
  i4 constant interval(4) not null := i_bare;
  i3 constant interval(3) not null := i_bare;
  i2 constant interval(2) not null := i_bare;
  i1 constant interval(1) not null := i_bare;
  i0 constant interval(0) not null := i_bare;

  r6 constant text := '10 years 6 mons 700 days 98:27:15.123456';
  r5 constant text := '10 years 6 mons 700 days 98:27:15.12346';

-- This shows the "round()" semantics.
  r4 constant text := '10 years 6 mons 700 days 98:27:15.1235';

  r3 constant text := '10 years 6 mons 700 days 98:27:15.123';
  r2 constant text := '10 years 6 mons 700 days 98:27:15.12';
  r1 constant text := '10 years 6 mons 700 days 98:27:15.1';
  r0 constant text := '10 years 6 mons 700 days 98:27:15';
begin
  -- Notice that i_bare, declared as bare "interval",
  -- and i6, declared as "interval(6)" are the same.
  assert i_bare ::text = r6, 'i_bare  = r6 failed';
  assert i6     ::text = r6, 'i6 = r6 failed';

  assert i5     ::text = r5, 'i5 = r5 failed';
  assert i4     ::text = r4, 'i4 = r4 failed';
  assert i3     ::text = r3, 'i3 = r3 failed';
  assert i2     ::text = r2, 'i2 = r2 failed';
  assert i1     ::text = r1, 'i1 = r1 failed';
  assert i0     ::text = r0, 'i0 = r0 failed';
end;
$body$;

The block finishes silently showing that all the assertions hold. This confirms that the (p) element determines the precision, in microseconds, with which the seconds field of the internal representation records the value. Repeat the test after globally replacing interval with interval second in the declarations. The outcome is identical.

Modeling the implementation

Make sure that you've read the section How does YSQL represent an interval value? before reading this section.

The assumption that informs the following test is that any operation that produces an interval value first computes the [mm, dd, ss] internal representation and only then applies the rules that the ad hoc test above illustrates. The rule, expressed in PL/pgSQL code, is shown in the Summary above.

The test provides new overloads for these two functions:

  • function interval_mm_dd_ss (interval_parameterization_t)

  • function interval_value (interval_parameterization_t)

that each adds a mode text formal parameter to express the class name of the interval declaration as one of 'bare', 'year', 'month', 'day', 'hour', 'minute', or 'second', thus:

drop function if exists interval_mm_dd_ss(interval_parameterization_t, text) cascade;

create function interval_mm_dd_ss(p in interval_parameterization_t, mode in text)
  returns interval_mm_dd_ss_t
  language plpgsql
as $body$
declare
  -- Use the single-parameter overload
  mm_dd_ss  constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(p);

  mm                 int                 not null := mm_dd_ss.mm;
  dd                 int                 not null := mm_dd_ss.dd;
  ss                 double precision    not null := mm_dd_ss.ss;
begin
  case mode
    when 'bare' then
      null;
    when 'second' then
      null;
    when 'minute' then
      ss := trunc(ss/60.0)*60.0;
    when 'hour' then
      ss := trunc(ss/(60.0*60.0))*60.0*60.0;
    when 'day' then
      ss := 0.0;
    when 'month' then
      ss := 0.0;
      dd := 0;
    when 'year' then
      ss := 0.0;
      dd := 0;
      mm := (mm/12)*12; -- integer division uses "trunc()" semantics
  end case;
  return (mm, dd, ss)::interval_mm_dd_ss_t;
end;
$body$;

and:

drop function if exists interval_value(interval_parameterization_t, text) cascade;

create function interval_value(p in interval_parameterization_t, mode in text)
  returns interval
  language plpgsql
as $body$
declare
  -- Use the single-parameter overload
  i_bare   constant interval        not null := interval_value(p);

  i_year   constant interval year   not null := i_bare;
  i_month  constant interval month  not null := i_bare;
  i_day    constant interval day    not null := i_bare;
  i_hour   constant interval hour   not null := i_bare;
  i_minute constant interval minute not null := i_bare;
  i_second constant interval second not null := i_bare;
begin
  return
    case mode
      when 'bare'   then i_bare
      when 'year'   then i_year
      when 'month'  then i_month
      when 'day'    then i_day
      when 'hour'   then i_hour
      when 'minute' then i_minute
      when 'second' then i_second
    end;
end;
$body$;

Test the modeled implementation of the constraints in the same way that the unconstrained model was tested in the section Modeling the internal representation and comparing the model with the actual implementation. The procedure assert_model_ok_worker() has the identical implementation to procedure assert_model_ok() in that section.

drop procedure if exists assert_model_ok_worker(interval_parameterization_t, text) cascade;

create procedure assert_model_ok_worker(p in interval_parameterization_t, mode in text)

  language plpgsql
as $body$
declare
  i_modeled        constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(p);
  i_from_modeled   constant interval            not null := interval_value(i_modeled);
  i_actual         constant interval            not null := interval_value(p);
  mm_dd_ss_actual  constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i_actual);

  p_modeled  constant interval_parameterization_t not null := parameterization(i_modeled);
  p_actual   constant interval_parameterization_t not null := parameterization(i_actual);
begin
  -- Belt-and-braces check for mutual consistency among the "interval" utilities.
  assert (i_modeled      ~= mm_dd_ss_actual), 'assert #1 failed';
  assert (p_modeled      ~= p_actual       ), 'assert #2 failed';
  assert (i_from_modeled == i_actual       ), 'assert #3 failed';
end;
$body$;

drop procedure if exists assert_model_ok(interval_parameterization_t) cascade;

create procedure assert_model_ok(p in interval_parameterization_t)
  language plpgsql
as $body$
begin
  call assert_model_ok_worker(p, 'bare');
  call assert_model_ok_worker(p, 'year');
  call assert_model_ok_worker(p, 'month');
  call assert_model_ok_worker(p, 'day');
  call assert_model_ok_worker(p, 'hour');
  call assert_model_ok_worker(p, 'minute');
  call assert_model_ok_worker(p, 'second');
end;
$body$;

Execute the tests using the same procedure test_internal_interval_representation_model() that was defined and used to test the unconstrained model. (This is also included in the code kit.)

call test_internal_interval_representation_model();

Each of the tests finishes silently showing that the rules explained above have so far been shown always to agree with the rules of the actual implementation. You are challenged to disprove the hypothesis by inventing more tests. If any of your tests causes assert_model_ok() to finish with an assert failure, then raise a GitHub issue against this documentation section.

If you do find such a counter-example, you can compare the results from using the actual implementation and the modeled implementation with this re-write of the logic of the assert_model_ok() procedure. Instead of using assert, it shows you the outputs for visual comparison. It has an almost identical implementation to the function model_vs_actual_comparison() in the section Modeling the internal representation and comparing the model with the actual implementation. The difference is that the following implentation adds the formal input parameter mode and carries this through to the new overload of interval_mm_dd_ss().

drop function if exists model_vs_actual_comparison(interval_parameterization_t, text) cascade;

create function model_vs_actual_comparison(p in interval_parameterization_t, mode in text)
  returns table(x text)
  language plpgsql
as $body$
declare
  i_modeled        constant interval_mm_dd_ss_t         not null := interval_mm_dd_ss(p, mode);
  i_actual         constant interval                    not null := interval_value(p, mode);

  p_modeled        constant interval_parameterization_t not null := parameterization(i_modeled);
  p_actual         constant interval_parameterization_t not null := parameterization(i_actual);

  ss_modeled_text  constant text                        not null := ltrim(to_char(p_modeled.ss, '9999999999990.999999'));
  ss_actual_text   constant text                        not null := ltrim(to_char(p_actual.ss,  '9999999999990.999999'));
begin
  x := 'modeled: '||
    lpad(p_modeled.yy ::text,  4)||' yy, '||
    lpad(p_modeled.mm ::text,  4)||' mm, '||
    lpad(p_modeled.dd ::text,  4)||' dd, '||
    lpad(p_modeled.hh ::text,  4)||' hh, '||
    lpad(p_modeled.mi ::text,  4)||' mi, '||
    lpad(ss_modeled_text,     10)||' ss';                           return next;

  x := 'actual:  '||
    lpad(p_actual.yy  ::text,  4)||' yy, '||
    lpad(p_actual.mm  ::text,  4)||' mm, '||
    lpad(p_actual.dd  ::text,  4)||' dd, '||
    lpad(p_actual.hh  ::text,  4)||' hh, '||
    lpad(p_actual.mi  ::text,  4)||' mi, '||
    lpad(ss_actual_text,      10)||' ss';                           return next;
end;
$body$;

Use it like this:

select x from model_vs_actual_comparison(interval_parameterization(
  yy => -9.7,
  mm =>  1.55,
  dd => -17.4,
  hh =>  99.7,
  mi => -86.7,
  ss =>  75.7),
  'day');

This is the result:

 modeled:   -9 yy,   -7 mm,   -1 dd,    0 hh,    0 mi,   0.000000 ss
 actual:    -9 yy,   -7 mm,   -1 dd,    0 hh,    0 mi,   0.000000 ss
  • Summary
  • Interval declaration syntax variants
  • Syntax variants within each of just six groups have the same effect
  • The effect of the optional (p) element
  • Modeling the implementation
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2022 Yugabyte, Inc. All rights reserved.