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 > General-purpose functions > Miscellaneous >

Function extract() | date_part() returns double precision

Report a doc issue Suggest new content Contributor guide
  • List of keywords
  • Which fields can you extract from values of which data type?

The function extract(), and the alternative syntax that the function date_part() supports for the same semantics, return a double precision value corresponding to a nominated so-called field, like year or second, from the input date-time value.

The two functions, extract() and date_part(), have identical semantics. They differ not just in name but also in syntax:

extract(<field> from date_time_value) == date_part(field_text_value, date_time_value)

Notice that extract() requires that is a keyword while date_part() requires that field_text_value is a text value (expression).

Try this:

set timezone = 'America/Los_Angeles';
with
  c1 as (
    select '2021-09-22 13:17:53.123456 Europe/Helsinki'::timestamptz as t),
  c2 as (
  select
    extract(week   from t) as ew,
    extract(hour   from t) as eh,
    extract(second from t) as es,

    date_part('week',   t) as dw,
    date_part('hour',   t) as dh,
    date_part('second', t) as ds
  from c1)
select
  ew::text as "week",
  eh::text as "hour",
  es::text as "second",

  (ew = dw)::text as "(ew = dw)",
  (eh = dh)::text as "(eh = dh)",
  (es = ds)::text as "(es = ds)"
from c2;

This is the result:

 week | hour |  second   | (ew = dw) | (eh = dh) | (es = ds)
------+------+-----------+-----------+-----------+-----------
 38   | 3    | 53.123456 | true      | true      | true

The extract() function is specified in the SQL Standard and seems to be supported in all SQL database systems. The list of keywords, though, is database-system-specific. (For example, Oracle Database supports only about ten of these while PostgreSQL, and therefore YSQL, support about twenty.) In contrast, the date_part() function is specific to PostgreSQL (and any system like YSQL that aims to support the identical syntax and semantics).

The \df metacommand produces output for date_part() in the normal way; but it produces no output for extract(). Here is the interesting part of the output from \df date_part():

 Result data type |        Argument data types
------------------+-----------------------------------
 double precision | text, date

 double precision | text, time without time zone

 double precision | text, timestamp without time zone
 double precision | text, timestamp with time zone

 double precision | text, interval

Three rows were removed manually:

  • The one for the timetz argument data type. (See the recommendation to avoid using timetz on the overall date-time section's main page.)
  • The two for the abstime and reltim data types. (See the recommendation below.)

The remaining rows were re-ordered, and blank lines were added, to improve the readability.

Avoid using the 'abstime' and 'reltime' fields.

The PostgreSQL documentation says this:

The data types abstime and reltime are lower precision types which are used internally. Don't use these types in applications; these internal types might disappear in a future release.

Because date_part() uses a text value to specify the to-be-extracted field rather than a keyword, it allows noticeably more flexible programmability. Here's a simple example:

drop function if exists fields_report(timestamptz) cascade;

create function fields_report(tstz in timestamptz)
  returns table(z text)
  language plpgsql
as $body$
declare
  fields constant text[] not null := array ['year', 'month', 'day', 'hour', 'minute', 'second'];
  f               text   not null := '';
begin
  foreach f in array fields loop
    assert pg_typeof(date_part(f, tstz))::text = 'double precision';
    z := rpad(f||':', 8)||date_part(f, tstz)::text; return next;
  end loop;
end;
$body$;

Test it first like this:

set timezone = 'UTC';
select z from fields_report('2017-05-17 11:42:13 UTC');

This is the result:

 year:   2017
 month:  5
 day:    17
 hour:   11
 minute: 42
 second: 13

Now test it like this:

set timezone = 'America/Los_Angeles';
select z from fields_report('2021-09-22 13:17:53.123456 Europe/Helsinki');

This is the result:

 year:   2021
 month:  9
 day:    22
 hour:   3
 minute: 17
 second: 53.123456

You can see that, in the normal way, the extracted value for the months field from a timestamptz value is sensitive both to the reigning timezone when the value is set and the reigning timezone when it is observed.

List of keywords

Keyword Description
millennium The millennium.
century The century.
decade The year field divided by 10.
year The year field. Remember that there is no year zero, so subtracting BC years from AD years should be done with care. (Add one to the result.)
quarter The quarter of the year (1..4) that the date is in.
month For timestamp[tz] values, the number of the month within the year (1..12); for interval values, the number of months, modulo 12 (0..11).
day For timestamp[tz] values, the day (of the month) field (1..31); for interval values, the number of days.
hour The hour field (0..23).
minute The minutes field (0..59).
second The seconds field, including fractional parts (0..59.999999).
milliseconds The seconds field, including fractional parts, multiplied by 1,000. Note that this includes full seconds.
microseconds The seconds field, including fractional parts, multiplied by 1,000,000. Note that this includes full seconds.
timezone_hour The hour component of the time zone offset.
timezone_minute The minute component of the time zone offset.
timezone The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.
doy The day of the year (1..365/366).
dow The day of the week as Sunday (0) to Saturday (6).
isodow The day of the week as Monday (1) to Sunday (7).
week The number of the week of the year using the ISO 8601 week-numbering scheme. By definition, ISO weeks start on Mondays and the first week of a year contains 4-January of that year. In other words, the first Thursday of a year is in week 1 of that year. In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of 2004, and 2006-01-01 is part of the 52nd week of 2005, while 2012-12-31 is part of the first week of 2013. Use the isoyear field together with the week field to get consistent results. See the example immediately after this table.
isoyear The ISO 8601 week-numbering year that the date falls in (not applicable to intervals).
epoch For timestamptz values, the result is the number of seconds since 1970-01-01 00:00:00 UTC (negative for values before that moment); and for date and plain timestamp values, the result is the number of seconds since 1970-01-01 00:00:00. See the demonstration subsection and the discussion the parcedes it on the plain timestamp and timestamptz data types page. For interval values, the result is the total number of seconds in the interval. See The extract(epoch from interval_value) built-in function. Notice that the result of extract(epoch from ...) is never sensitive to the session's timezone setting.
julian The Julian Date corresponding to the date or timestamp[tz] value (not supported for interval values). timestamp[tz] values that are not local midnight result in a fractional value. See Appendix B.7. Julian Dates in the PostgreSQL documentation.

Demonstrate the recommended joint use of isoyear and week thus:

drop function if exists f(date) cascade;
create function f(d in date)
  returns text
  language plpgsql
as $body$
begin
  return
    d::text||' -> '||
    ltrim(to_char(extract(isoyear from d), '9999999'))||':'||
    ltrim(to_char(extract(week    from d),      '09'));
end;
$body$;

select
  f('2005-01-01') as "y:d 1",
  f('2006-01-01') as "y:d 2",
  f('2012-12-31') as "y:d 3";

This is the result:

         y:d 1         |         y:d 2         |         y:d 3
-----------------------+-----------------------+-----------------------
 2005-01-01 -> 2004:53 | 2006-01-01 -> 2005:52 | 2012-12-31 -> 2013:01

Which fields can you extract from values of which data type?

Obviously, it makes sense to ask about the value of the timezone only for an observed timestamptz value. And because an interval value measures a duration and isn't anchored to any particular moment, it makes no sense to ask which millennium it falls in. The following table function attempts to extract, in turn, each of the fields with legal names (there are twenty-two in all) from values of each of the five relevant date-time data types, date, plain time, plain timestamp, timestamptz, and interval. (timetz values are excluded from the attempt following the recommendation, here, to avoid this data type. A table function is used so that the results can be presented in a nicely comprehensible fashion as a five-by-twenty-two matrix with the filed name on the vertical axis and the data type on the horizontal axis. If the requested extraction is legal, then the text typecast of resulting double precision value is shown in the cell. And if the operation is illegal, then the cell is left blank.

Create and execute the table function like this:

drop function if exists field_versus_data_type_extractability() cascade;

create function field_versus_data_type_extractability()
  returns table(z text)
  language plpgsql
as $body$
declare
  d       constant date        not null := '2016-09-18';
  t       constant time        not null := '13:17:53.123456';
  ts      constant timestamp   not null := '2016-09-18 13:17:53.123456';
  tstz    constant timestamptz not null := '2016-09-18 13:17:53.123456 Europe/Helsinki';
  i                interval    not null := make_interval(months=>14653, days=>99, secs=>5767.123456);

  pad     constant int         not null := 18;
  f                text        not null := '';
  v_d              text        not null := 0;
  v_t              text        not null := 0;
  v_ts             text        not null := 0;
  v_tstz           text        not null := 0;
  v_i              text        not null := 0;
  fields  constant text[]           not null := array [
    'millennium',
    'century',
    'decade',
    'year',
    'quarter',
    'month',
    'day',
    'hour',
    'minute',
    'second',
    'milliseconds',
    'microseconds',
    'timezone_hour',
    'timezone_minute',
    'timezone',

    'doy',
    'dow',
    'isodow',
    'week',
    'isoyear',

    'epoch',
    'julian'
  ];
begin
  z := rpad('date:',     pad)||d::text;                                                 return next;
  z := rpad('time:',     pad)||t::text;                                                 return next;
  z := rpad('ts:',       pad)||ts::text;                                                return next;
  z := rpad('tstz:',     pad)||tstz::text;                                              return next;
  z := rpad('interval:', pad)||i::text;                                                 return next;
  z:= '';                                                                               return next;

  z := lpad(' ', pad)||lpad('date',     pad)||
                       lpad('time',     pad)||
                       lpad('ts',       pad)||
                       lpad('tstz',     pad)||
                       lpad('interval', pad);                                           return next;

  z := lpad(' ', pad)||'  '||lpad('-', (pad-2), '-')||
                       '  '||lpad('-', (pad-2), '-')||
                       '  '||lpad('-', (pad-2), '-')||
                       '  '||lpad('-', (pad-2), '-')||
                       '  '||lpad('-', (pad-2), '-');                                   return next;


  foreach f in array fields loop

    -- "feature_not_supported"   is "0A000"
    -- "invalid_parameter_value" is "22023"
    begin
      v_d := date_part(f, d)::text;
    exception
      when feature_not_supported then
        v_d := '';
    end;

    begin
      v_t := date_part(f, t)::text;
    exception
      when feature_not_supported or invalid_parameter_value then
        v_t := '';
    end;

    begin
      v_ts := date_part(f, ts)::text;
    exception
      when feature_not_supported or invalid_parameter_value then
        v_ts := '';
    end;

    v_tstz := date_part(f, tstz)::text;

    begin
      v_i := date_part(f, i)::text;
    exception
      when feature_not_supported or invalid_parameter_value then
        v_i := '';
    end;

    z := rpad(f||':', pad)||lpad(v_d,    pad)||
                           lpad(v_t,    pad)||
                           lpad(v_ts,   pad)||
                           lpad(v_tstz, pad)||
                           lpad(v_i,    pad);                                           return next;
  end loop;
end;
$body$;

select z from field_versus_data_type_extractability();

This is the result:

 date:             2016-09-18
 time:             13:17:53.123456
 ts:               2016-09-18 13:17:53.123456
 tstz:             2016-09-18 03:17:53.123456-07
 interval:         1221 years 1 mon 99 days 01:36:07.123456

                                 date              time                ts              tstz          interval
                     ----------------  ----------------  ----------------  ----------------  ----------------
 millennium:                        3                                   3                 3                 1
 century:                          21                                  21                21                12
 decade:                          201                                 201               201               122
 year:                           2016                                2016              2016              1221
 quarter:                           3                                   3                 3                 1
 month:                             9                                   9                 9                 1
 day:                              18                                  18                18                99
 hour:                              0                13                13                 3                 1
 minute:                            0                17                17                17                36
 second:                            0         53.123456         53.123456         53.123456          7.123456
 milliseconds:                      0         53123.456         53123.456         53123.456          7123.456
 microseconds:                      0          53123456          53123456          53123456           7123456
 timezone_hour:                                                                          -7
 timezone_minute:                                                                         0
 timezone:                                                                           -25200
 doy:                             262                                 262               262
 dow:                               0                                   0                 0
 isodow:                            7                                   7                 7
 week:                             37                                  37                37
 isoyear:                        2016                                2016              2016
 epoch:                    1474156800      47873.123456  1474204673.12346  1474193873.12346  38542980967.1235
 julian:                      2457650                    2457650.55408708  2457650.13742041

Notice that an illegal extraction attempt with all data types apart from time causes the 0A000 error (feature_not_supported). But an illegal extraction attempt on a time value might cause the 22023 error (invalid_parameter_value) with a message like "time" units "millennium" not recognized.

The rules that let you predict the extracted field values from an interval value rely on understanding how interval values are stored as a [mm, dd, ss] tuple. This is explained in the section How does YSQL represent an interval value? Further, the algorithm that extract() uses on an interval value depends on some arbitrarily asserted rules of thumb, explained here.

  • List of keywords
  • Which fields can you extract from values of which data type?
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2022 Yugabyte, Inc. All rights reserved.