REW

How To Extract Time From Timestamp In PostgreSQL?

Published Aug 29, 2025 3 min read
On this page

The simplest and most direct method to extract the time from a PostgreSQL timestamp is to cast it to the TIME data type. For more control over the format, the TO_CHAR() function is the most flexible and powerful option. To extract individual time components, use the EXTRACT() or DATE_PART() functions.

Method 1: Casting to TIME

Casting a TIMESTAMP to the TIME data type is the most straightforward way to get the time of day. This method is concise and standard for extracting the time component, especially when you need to store or compare it as a TIME object.

Syntax

SELECT your_timestamp_column::TIME;

Use code with caution.

Example

Assume you have a table events with a created_at column of type TIMESTAMP.

Query

SELECT created_at, created_at::TIME AS event_time
FROM events;

Use code with caution.

Output

created_at event_time
2025-08-29 09:30:15 09:30:15
2025-08-29 14:00:00 14:00:00

Analysis

  • Pros: Very simple and readable. Returns a TIME data type, which is useful for time-specific comparisons.
  • Cons: Not suitable for custom formatting, such as displaying a 12-hour clock with AM/PM.

Method 2: Using TO_CHAR() for custom formatting

The TO_CHAR() function is highly versatile and converts any date/time value to a formatted string. This method is ideal for displaying the time in a human-readable format.

Syntax

SELECT TO_CHAR(your_timestamp_column, 'format_mask');

Use code with caution.

Common format masks for time

Mask Description
HH12 Hour of the day (01–12)
HH24 Hour of the day (00–23)
MI Minute (00–59)
SS Second (00–59)
AM or PM Meridiem indicator (e.g., AM or PM)
FF series Fractional seconds (e.g., FF3 for milliseconds)

Examples

12-hour format with AM/PMQuery

SELECT TO_CHAR(TIMESTAMP '2025-08-29 14:30:15', 'HH12:MI:SS AM');

Use code with caution.

Output02:30:15 PM

24-hour format with millisecondsQuery

SELECT TO_CHAR(TIMESTAMP '2025-08-29 14:30:15.123', 'HH24:MI:SS.FF3');

Use code with caution.

Output14:30:15.123

Analysis

  • Pros: Offers complete control over the output format.
  • Cons: Returns a TEXT data type, which is not suitable for time-based calculations or comparisons unless re-cast. The format mask requires memorization or referencing.

Method 3: Using EXTRACT() and DATE_PART()

The EXTRACT() and DATE_PART() functions extract individual components of a timestamp, such as the hour, minute, or second. EXTRACT() is the SQL standard, while DATE_PART() is a historical PostgreSQL function.

Syntax

SELECT EXTRACT(field FROM source);
SELECT DATE_PART('field', source);

Use code with caution.

Field values for time extraction

Field Description EXTRACT() example DATE_PART() example
HOUR The hour field (0-23) EXTRACT(HOUR FROM '2025-08-29 10:30:15'::timestamp) DATE_PART('hour', '2025-08-29 10:30:15'::timestamp)
MINUTE The minute field (0-59) EXTRACT(MINUTE FROM '2025-08-29 10:30:15'::timestamp) DATE_PART('minute', '2025-08-29 10:30:15'::timestamp)
SECOND The seconds field, including fractional seconds EXTRACT(SECOND FROM '2025-08-29 10:30:15.123'::timestamp) DATE_PART('second', '2025-08-29 10:30:15.123'::timestamp)
MILLISECONDS The seconds field multiplied by 1000 EXTRACT(MILLISECONDS FROM '2025-08-29 10:30:15.123'::timestamp) DATE_PART('milliseconds', '2025-08-29 10:30:15.123'::timestamp)

Analysis

  • Pros: Returns numeric values, making it useful for mathematical operations, aggregation, and grouping by a specific time component.
  • Cons: Requires combining multiple parts if you want the full time string, which can be less efficient than TO_CHAR() for simple formatting.

Summary comparison of methods

Feature Casting (::TIME) TO_CHAR() EXTRACT() / DATE_PART()
Primary Use Get the time of day as a TIME object Custom formatting for display Extract individual numeric components
Output Type TIME TEXT DOUBLE PRECISION (numeric)
Flexibility Low (fixed format) High (fully customizable) Medium (returns specific parts)
Performance Very fast for simple extraction Slower than casting due to formatting overhead Can be faster for single component extraction
Use Case Time-of-day calculations and comparisons Creating human-readable reports and user-facing displays Aggregating or analyzing data by hour, minute, etc.
Enjoyed this article? Share it with a friend.