Sling Academy
Home/PostgreSQL/Time of day formats and input/output in PostgreSQL

Time of day formats and input/output in PostgreSQL

Last updated: January 05, 2024

Introduction

Managing time-of-day data efficiently can be quite challenging, particularly when dealing with databases. PostgreSQL, with its robust set of time-related functions and types, makes it easier to input, store, and retrieve time of day in various formats.

Understanding Time Types in PostgreSQL

In PostgreSQL, the TIME data type is used to represent the time of day without date information. To handle time data, you should be familiar with the time-related data types such as TIME, TIMETZ (time with time zone), and their associated functions.

Basic Time Input/Output

You can input time values into PostgreSQL using a variety of formats, such as ISO 8601, SQL standard, or traditional PostgreSQL format. For example:

INSERT INTO schedule (appointment) VALUES ('13:30:15');

To retrieve this data, the same formats are supported:

SELECT appointment FROM schedule;

PostgreSQL will display the time in a human-readable format, generally in the default configuration defined by the server’s locale settings.

Formatting Time Output

To explicitly format time data when retrieving it, you can use the to_char() function to convert a time value to a textual representation. For example, to format a time as hours and minutes only:

SELECT to_char(appointment, 'HH24:MI') FROM schedule;

Working with Time Zones

To handle time zones, you utilize the TIMETZ data type which stores time with a time zone offset from UTC. When inserting a TIMETZ value, specify the time zone:

INSERT INTO world_schedule (appointment) VALUES ('13:30:15+02');

When you select this data, PostgreSQL will automatically convert it to the time zone of the server if a specific output time zone is not specified:

SET timezone = 'UTC';
SELECT appointment FROM world_schedule;

For accurate time zone conversions, you’ll want to operate with TIMESTAMP WITH TIME ZONE instead of just TIME or TIMETZ.

Extraction and Truncation

SQL provides functions to extract elements from a time, such as hours, minutes, and seconds:

SELECT EXTRACT(HOUR FROM appointment) AS hour, EXTRACT(MINUTE FROM appointment) AS minute FROM schedule;

If you want to truncate a time to rounded down values like the nearest hour, you can use:

SELECT date_trunc('hour', appointment) FROM schedule;

Interval Arithmetic

Time values often need to be manipulated using intervals, which represent durations. You can add or subtract an INTERVAL to a TIME:

SELECT appointment + INTERVAL '1 hour' FROM schedule;
SELECT appointment - INTERVAL '30 minutes' FROM schedule;

Advanced Usage and Functions

For more advanced needs, PostgreSQL allows for a range of functions such as age, overlapping time ranges using the tsrange data type, and extracting epoch from time-based data:

SELECT age(TIMESTAMP '2023-01-01 14:00', TIMESTAMP '2023-01-01 13:00');
SELECT tstzrange(startTime, endTime) OVERLAPS (current_timestamp, INTERVAL '1 hour');
SELECT EXTRACT(EPOCH FROM appointment) FROM schedule;

These functions make handling different time-related queries far more robust and can be particularly useful in applications that require precise timekeeping or scheduling.

Conclusion

In conclusion, PostgreSQL’s handling of time of day through its various data types and functions is powerful and customizable. Basic manipulation using intervals and formatting outputs allows for extensive flexibility while more advanced features like time zone handling and epoch extraction expand the potential for more complex applications. By understanding and utilizing these tools, you can manage time data efficiently in PostgreSQL.

Next Article: Strings to Dates conversion in PostgreSQL

Previous Article: 3 Ways to See the Structure of a Table in PostgreSQL

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB