DEV Community

Cover image for Working with PostgreSQL Date and Time Functions
Gichuki Edwin
Gichuki Edwin

Posted on

Working with PostgreSQL Date and Time Functions

Columns filled with dates and times provide valuable insights into when events occurred or how long they lasted. Analyzing patterns on a timeline, identifying the shortest or longest events, or discovering relationships between activities and specific times of day or seasons are all possible with the right tools. PostgreSQL’s data types for dates and times, along with its related functions, empower users to explore these kinds of questions effectively.

Data Types and Functions for Dates and Times

PostgreSQL provides four main data types for handling dates and times:

1. DATE

  • Records only the date.
  • Accepts various formats, such as:
    • November 19, 2024
    • 11/19/2024
    • 2024-11-19 (recommended, ISO 8601 standard).

2. TIME

  • Records only the time.
  • Format: HH:MM:SS.
  • Adding with time zone makes the column time-zone aware.
  • Optional time zone designator can be added.

3. TIMESTAMP

  • Records both date and time.
  • Adding with time zone (shorthand: timestamptz) makes the column time-zone aware.
  • Format: YYYY-MM-DD HH:MM:SS TZ.
  • Time zones can be specified as:
    • UTC offset
    • Area/location designator
    • Standard abbreviation.

4. INTERVAL

  • Represents a duration of time.
  • Format: quantity unit (e.g., 69 days, 18 months, 3 years).

The first three are datetime types, while INTERVAL represents intervals.


Manipulating Dates and Times

PostgreSQL provides functions to perform calculations or extract components from date and time values. For example, you can retrieve the day of the week from a timestamp or extract the month from a date.

Extracting Components from a Timestamp

When aggregating data by month, year, or minute, extracting specific components of a timestamp is often necessary. PostgreSQL offers two main functions:

1. date_part() Function

  • Format: date_part(text, value).
  • Arguments:
    • A string representing the part to extract (e.g., hour, minute).
    • A date, time, or timestamp value.
  • Returns values of type double precision (precision issues may arise).

2. EXTRACT() Function

  • Recommended for precision.
  • Format: EXTRACT(field FROM source).
    • source: A value of type timestamp, date, time, or interval.
    • field: A string indicating the component to extract.
  • Returns values of type numeric.

Example Usage of EXTRACT()

SELECT
    EXTRACT(year FROM '2024-11-20 10:33:12+03'::timestamptz) AS year,
    EXTRACT(month FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS month,
    EXTRACT(day FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS day,
    EXTRACT(hour FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS hour,
    EXTRACT(minute FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS minute,
    EXTRACT(second FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS second,
    EXTRACT(timezone_hour FROM '2024-11-20 10:33:12+03'::timestamptz) AS tz_hour,
    EXTRACT(timezone_minute FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS tz_minute,
    EXTRACT(epoch FROM '2024-11-20 10:33:12 EAT'::timestamptz) AS epoch;
Enter fullscreen mode Exit fullscreen mode

Key Field Explanations:

  1. DOW: Day of the week (Sunday = 0, Saturday = 6).
  2. DOY: Day of the year (1-365/366).
  3. epoch: Seconds since 1970-01-01 00:00:00 UTC.
    • Convert back with to_timestamp(epoch).
  4. isodow: Day of the week (Monday = 1, Sunday = 7).
  5. isoyear: ISO 8601 week-numbering year.
  6. millennium: Millennium (year divided by 1000).

Creating Datetime Values from Components

To perform calculations on dates, it's often necessary to combine components into a single datetime value. PostgreSQL provides the following functions:

  1. make_date(year, month, day)

    • Returns a value of type date.
  2. make_time(hour, minute, second)

    • Returns a value of type time (without time zone).
  3. make_timestamptz(year, month, day, hour, minute, second, time zone)

    • Returns a timestamp with time zone.

Example:

SELECT
    make_date(2024, 11, 20) AS date_value,
    make_time(10, 33, 12) AS time_value,
    make_timestamptz(2024, 11, 20, 10, 33, 12, 'EAT') AS timestamptz_value;
Enter fullscreen mode Exit fullscreen mode

Conclusion

PostgreSQL’s robust date and time functions allow for comprehensive analysis of temporal data. By understanding the data types and leveraging the appropriate functions, you can extract insights, perform calculations, and manipulate data to suit a variety of analytical needs.

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 👀

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay