Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL DATE

PostgreSQL Date Functions are used to manipulate and extract information from date and time values, specifically those of types such as date, timestamp, and interval. These functions help perform operations like calculating intervals, extracting parts of a date, and formatting dates.

Usage

Date Functions in PostgreSQL are utilized when you need to perform calculations or extract specific components from date and time data. They are essential for tasks like reporting, scheduling, and time-based data analysis.

SELECT function_name(date_column)
FROM table_name;

In this syntax, function_name represents a specific date function applied to date_column to perform the desired operation.

Examples

1. Extracting the Year

SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

This example uses the EXTRACT function with the syntax EXTRACT(field FROM source) to retrieve the year component from the order_date column, returning it as order_year. EXTRACT can also be used for other components like month, day, hour, etc.

2. Adding Days to a Date

SELECT order_date + INTERVAL '10 days' AS new_date
FROM orders;

This syntax demonstrates how to add 10 days to each order_date, resulting in a new date called new_date. Here, INTERVAL is a specific data type used for handling time intervals.

3. Calculating Date Differences

SELECT age(delivery_date, order_date) AS delivery_duration
FROM orders;

Here, the age function calculates the difference between the delivery_date and order_date, returning the duration as delivery_duration. Note that AGE returns an interval format, which might need conversion for display or further calculations.

Tips and Best Practices

  • Use appropriate functions. Choose the right date function based on the specific operation you need to perform, such as AGE for differences or EXTRACT for parts.
  • Format dates for readability. Use functions like TO_CHAR to convert dates into human-readable formats when needed.
  • Optimize date calculations. Leverage indexed date columns for faster queries, especially when filtering or joining tables.
  • Be mindful of time zones. When working with timestamps, ensure you account for time zones to avoid discrepancies, using functions like AT TIME ZONE.
  • Handle leap years and daylight saving time. Account for these in calculations to ensure accuracy.
  • Use date_trunc for truncating dates. This function is useful for grouping dates by month, quarter, etc.

Additional Date Functions

  • NOW(): Returns the current date and time.
  • CURRENT_DATE: Returns the current date.
  • JUSTIFY_DAYS: Adjusts intervals to normalize days and months.

These functions and others enhance the versatility of date and time manipulation in PostgreSQL, enabling comprehensive data analysis and management.