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
Interval arithmetic [YSQL]
> APIs > YSQL > Data types > Date and time > Semantics of the date-time data types > Interval data type >

Interval arithmetic

Report a doc issue Suggest new content Contributor guide

This section uses the term "moment" as an umbrella for a timestamptz value, a timestamp value, or a time value. (In a broader scenario, a date value is also a moment. But you get an integer value when you subtract one date value from another. And you cannot add or subtract an interval value to/from a date value.) The term "interval arithmetic" is used somewhat loosely to denote these three distinct scenarios:

  • The interval-interval overload of the "=" operator—interval-interval equality. This is what the term implies.

  • Interval-only addition/subtraction and multiplication/division: This has two subtopics:

    • First The interval-interval overloads of the "+" and "-" operators to produce a new interval value.

    • And second The interval-number overloads of the "*" and "/" operators to produce a new interval value.

  • moment-interval arithmetic: This has two subtopics:

    • First The moment-moment overloads of the "-" operator to produce an interval value. (Addition of two moments is meaningless and is therefore illegal. So is multiplication or division of a moment by a number.)

    • And second The moment-interval overloads of the "+" and "-" operators to produce a new moment of the same data type.

You need to understand the notions that the section Two ways of conceiving of time: calendar-time and clock-time addresses in order to understand the code and the explanations in this page's child page The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time. The notions help you understand how the semantic rules of the native moment-moment overloads of the "-" operator for timestamptz, timestamp, and time are ultimately confusing and therefore unhelpful—and why you should therefore adopt the practices that the section Custom domain types for specializing the native interval functionality explains. The distinction between clock-time-semantics and calendar-time-semantics is only implicitly relevant for the notions that the sections The interval-interval overload of the "=" operator and Interval-only addition/subtraction and multiplication/division explain.

The PostgreSQL documentation does not carefully specify the semantics of interval arithmetic. This page and its children aim to specify the operations in terms of the individual fields of the internal representation.

The results of 'interval' arithmetic are, in general, sensitive to the session's timezone.

More carefully stated, the results of some moment-interval arithmetic operations (the moment-moment overloads of the - operator and the moment-interval overloads of the + and - operators) are sensitive to the session's TimeZone setting when the moments are timestamptz values.

The interval-interval overload of the "=" operator

Try this:

select
  (
    '5 days  1 hours'::interval =
    '4 days 25 hours'::interval
  )::text as "'1 day' is defined to be equal to '24 hours'",
  (
    '5 months  1 day' ::interval =
    '4 months 31 days'::interval
  )::text as "'1 month' is defined to be equal to '30 days'";

The result for each equality expression is true. This is a strange definition of equality because there are 29 days in February in a leap year and otherwise 28 days, there are four 30 day months, and there are seven 31 day months. Further, 1 day is only usually 24 hours. (The usually caveat acknowledges the consequences of Daylight Savings Time changes.) All this crucially effects the semantics—see interval-moment arithmetic below.

The section Comparing two interval values explains the model that the interval overload of the = operator uses and tests it with a PL/pgSQL implementation. It also shows how to implement a user-defined interval-interval == operator that implements strict equality. The criterion for this is that each field of the LHS and RHS [mm, dd, ss] internal representations must be pairwise equal.

Interval-only addition/subtraction and multiplication/division

Empirical tests show the following:

  • The + operator and the - operator are overloaded to allow the addition and subtraction of two interval values. Here, the outcome can be understood in terms of pairwise field-by-field addition or subtraction of the two [mm, dd, ss] tuples.

  • The * operator and the / operator are overloaded to allow multiplication or division of an interval value by a real or integer number. Here, the outcome can be mainly understood in terms of multiplying, or dividing, the [mm, dd, ss] tuple, field-by-field, using the same factor. Notice the caveat mainly. In some rare corner cases, the model holds only when the forgiving built-in interval-interval = operator is used to compare the outcome of the model with that of the actual functionality. When the user-defined strict equality interval-interval ==operator is used, the tests show that, in these corner cases, the outcome of the model does not agree with that of the actual functionality.

In all cases of addition/subtraction and multiplication/division, the model assumes that a new intermediate [mm, dd, ss] tuple is produced and that each of the mm or dd fields might well be real numbers. It must be assumed that this intermediate value is then coerced into the required [integer, integer, real number] format using the same algorithm (see the section Modeling the internal representation and comparing the model with the actual implementation) that is used when such a tuple is provided in the ::interval typecast approach.

The interval-interval overloads of the "+" and "-" operators

The operation acts separately on the three individual fields of the internal representation adding or subtracting them pairwise:

  • [mm1, dd1, ss1] ± [mm2, dd2, ss2] = [(mm1 ± mm2), (dd1 ± dd2), (ss1 ± ss2)]

The section Adding or subtracting a pair of interval values simulates and tests the model for how this works in PL/pgSQL code.

Try this simple test:

select '2 months'::interval + '2 days'::interval;

This is the result:

 2 mons 2 days

This is consistent with the assumed model. And it shows that a practice that the user might adopt to use only interval values that have just a single non-zero internal representation field can easily be thwarted by interval-interval addition or subtraction.

The interval-number overloads of the "*" and "/" operators

The operation is assumed to be intended to act separately on each of the three individual fields:

  • [mm, dd, ss]*x = [mm*x, dd*x, ss*x]

When x is equal to f, where f > 1, the effect is multiplication by f. And when x is equal to 1/f, where f > 1, the effect is division by f. Therefore a single mental model explains both operations.

Try this positive test:

select
  '2 months 2 days'::interval*0.9                    as "result 1",
  '2 months'::interval*0.9 + '2 days'::interval*0.9  as "result 2";

This is the result:

        result 1        |        result 2
------------------------+------------------------
 1 mon 25 days 19:12:00 | 1 mon 25 days 19:12:00

It is consistent with the assumed model.

Now try this negative test:

select
  '2 months 2 days'::interval*0.97                     as "result 1",
  '2 months'::interval*0.97 + '2 days'::interval*0.97  as "result 1";

This is the result:

        result 1        |        result 1
------------------------+------------------------
 1 mon 30 days 03:21:36 | 1 mon 29 days 27:21:36

It is not consistent with the assumed model. But the only difference between the positive test and the negative test is that the former uses the factor 0.9 and the latter uses the factor 0.97.

Compare the apparently different results using the forgiving native interval-interval '=' operator like this:

select ('1 mon 30 days 03:21:36'::interval = '1 mon 29 days 27:21:36'::interval)::text;

The result is true. The section Multiplying or dividing an interval value by a number simulates and tests the model for how this works in PL/pgSQL code, and examines this unexpected outcome closely.

One thing, at least, is clear: a practice that the user might adopt to use only interval values that have just a single non-zero internal representation field can easily be thwarted by interval-number multiplication or division. Moreover, the semantics of these operations is not documented and cannot be reliably determined by empirical investigation. The outcomes must, therefore, be considered to be unpredictable.

Recommendation

Avoid native 'interval'-'interval' addition/subtraction and 'interval'-number multiplication/division.

Yugabyte recommends that you avoid performing operations whose results can easily thwart an adopted principle for good practice and especially that you avoid operations whose outcomes must be considered to be unpredictable. It recommends that instead you adopt the practice that the section Defining and using custom domain types to specialize the native interval functionality explains. Doing this will let you perform the addition, subtraction, multiplication, and division operations that are unsafe with native interval values in a controlled fashion that brings safety.

Moment-interval arithmetic

The - operator has a set of moment-moment overloads and a set of moment-interval overloads. The + operator has a set of -interval-moment overloads. The + operator has no moment-moment overloads. (This operation would make no sense.)

The moment-moment overloads of the "-" operator

The - operator has an overload for each pair of operands of the timestamptz, timestamp, and time data types. The result of subtracting two date values has data type integer. Try this:

drop function if exists f() cascade;
create function f()
  returns table(t text)
  language plpgsql
as $body$
declare
  d1 constant date           := '2021-01-13';
  d2 constant date           := '2021-02-17';

  t1 constant time           := '13:23:17';
  t2 constant time           := '15:37:43';

  ts1 constant timestamp     := '2021-01-13 13:23:17';
  ts2 constant timestamp     := '2021-02-17 15:37:43';

  tstz1 constant timestamptz := '2021-01-13 13:23:17 +04:00';
  tstz2 constant timestamptz := '2021-02-17 15:37:43 -01:00';

begin
  t := 'date:        '||(pg_typeof(d2    - d1   ))::text; return next;
  t := 'time:        '||(pg_typeof(t2    - t1   ))::text; return next;
  t := 'timestamp:   '||(pg_typeof(ts2   - ts1  ))::text; return next;
  t := 'timestamptz: '||(pg_typeof(tstz2 - tstz1))::text; return next;
end;
$body$;

select t from f();

This is the result:

 date:        integer
 time:        interval
 timestamp:   interval
 timestamptz: interval

The interval value that results from subtracting one moment from another (for the timestamptz, timestamp, or time data types) has, in general, a non-zero value for each of the dd and ss fields of the internal [mm, dd, ss] representation. The value of the mm field is always zero. The section The moment-moment overloads of the "-" operator for timestamptz, timestamp, and time explains the algorithm that produces the value and shows that, because it has two fields that have different rules for the semantics of the interval-moment overloads of the + and - operators, this approach for producing an interval value should be avoided. See the section Custom domain types for specializing the native interval functionality for the recommended alternative approach.

The moment-interval overloads of the "+" and "-" operators

The + and - operators have overloads for each pair of operands of each of the timestamptz, timestamp, and time data types with an interval operand. The notions that the section Two ways of conceiving of time: calendar-time and clock-time addresses are critical for the understanding of this functionality. The topic is explained carefully in the child page The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time. This test is copied from that page:

select (
    '30 days '::interval = '720 hours'::interval and
    ' 1 month'::interval = ' 30 days '::interval and
    ' 1 month'::interval = '720 hours'::interval
  )::text;

The result is true, showing that (at least in an inexact sense), the three spellings '720 hours', '30 days', and '1 month' all denote the same interval value. Critically, though, '720 hours' is a clock-time-semantics notion while '30 days' and '1 month' are each calendar-time-semantics notions, though in subtly different ways. This explains the outcome of this test—again, copied from (but in a simplified form) the child page:

drop table if exists t;
create table t(
   t0               timestamptz primary key,
  "t0 + 720 hours"  timestamptz,
  "t0 + 30 days"    timestamptz,
  "t0 + 1 month"    timestamptz);

insert into t(t0) values ('2021-02-19 12:00:00 America/Los_Angeles');

set timezone = 'America/Los_Angeles';

update t set
  "t0 + 720 hours" = t0 + '720 hours' ::interval,
  "t0 + 30 days"   = t0 + '30 days'   ::interval,
  "t0 + 1 month"   = t0 + '1 month'   ::interval;

select
  t0,
  "t0 + 720 hours",
  "t0 + 30 days",
  "t0 + 1 month"
from t;

This is the result:

           t0           |     t0 + 720 hours     |      t0 + 30 days      |      t0 + 1 month
------------------------+------------------------+------------------------+------------------------
 2021-02-19 12:00:00-08 | 2021-03-21 13:00:00-07 | 2021-03-21 12:00:00-07 | 2021-03-19 12:00:00-07

The fact that adding the (inexactly) "same" value produces three different results motivates the careful, and rather tricky, discussion of clock-time and the two sub-flavors of calendar-time (days versus months and years).

Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact us

Copyright © 2017-2022 Yugabyte, Inc. All rights reserved.