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

PostgreSQL TIMESTAMP

PostgreSQL provides robust support for date and time data types, including `TIMESTAMP WITHOUT TIME ZONE`, which stores date and time without a timezone. These features are essential for precise timekeeping and data manipulation tasks.

Usage

Date and time data types and functions, like `TIMESTAMP WITHOUT TIME ZONE`, are employed when you need to store precise date and time data or perform operations on such data. They are crucial for applications requiring accurate timekeeping, scheduling, and historical data analysis.

sql
SELECT column_name
FROM table_name
WHERE timestamp_column = TIMESTAMP 'YYYY-MM-DD HH:MI:SS';

In this syntax, `TIMESTAMP 'YYYY-MM-DD HH:MI:SS'` is used to define a specific date and time to compare against entries in `timestamp_column`.

Examples

1. Basic TIMESTAMP Usage

sql
SELECT order_id
FROM orders
WHERE order_date = TIMESTAMP '2023-10-15 14:30:00';

This query selects orders from the `orders` table where the `order_date` matches the specified timestamp.

2. Extracting Parts of a TIMESTAMP

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

Here, the `EXTRACT` function retrieves the year part from the `order_date` column for each row in the `orders` table.

3. Calculating Date Differences

sql
SELECT order_id, delivery_date - order_date AS delivery_duration
FROM orders;

This example calculates the duration between `order_date` and `delivery_date` for each order and returns it as `delivery_duration`.

4. Using TIMESTAMP WITH TIME ZONE

sql
SELECT order_id
FROM orders
WHERE order_date AT TIME ZONE 'UTC' = TIMESTAMP '2023-10-15 14:30:00';

This example demonstrates using `TIMESTAMP WITH TIME ZONE` to account for timezone differences by converting `order_date` to a specific timezone.

5. Truncating Dates with DATE_TRUNC

sql
SELECT DATE_TRUNC('month', order_date) AS month_start
FROM orders;

This query uses the `DATE_TRUNC` function to truncate `order_date` to the start of the month for each record.

Tips and Best Practices

  • Use appropriate precision. Consider using `TIMESTAMP WITH TIME ZONE` if your application requires awareness of timezones.
  • Index timestamp columns. Indexing can significantly speed up queries involving timestamp comparisons. However, indexing strategies may vary based on query patterns and should be evaluated on a case-by-case basis.
  • Be consistent with formats. Always use the same date-time format to avoid confusion and errors.
  • Utilize date functions wisely. Functions like `AGE`, `NOW`, and `DATE_TRUNC` can simplify complex date calculations.
  • Beware of daylight saving time. When using `TIMESTAMP WITH TIME ZONE`, PostgreSQL handles daylight saving time changes by automatically adjusting the stored time to maintain accuracy.