Skip to content
SQLScalar and Aggregate Functionsbeginner45 min read

Date and Time Functions

Work with temporal data: CURRENT_DATE, NOW(), EXTRACT, DATE_PART, and interval arithmetic.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Understand PostgreSQL date, time, timestamp, and interval types
  • ✓Retrieve the current date using CURRENT_DATE
  • ✓Retrieve the current transaction timestamp using NOW() and CURRENT_TIMESTAMP
  • ✓Extract year, month, day, and hour using EXTRACT and DATE_PART
  • ✓Add and subtract intervals from dates and timestamps
  • ✓Filter records using date ranges
  • ✓Understand timestamp time zones and transaction timestamp behavior
  • ✓Avoid common date filtering and indexing mistakes

1. Introduction to Date and Time Functions

Date and time functions help applications store, retrieve, compare, and calculate temporal information.

Examples of real-world uses include:
- Finding orders placed today.
- Calculating a delivery date seven days from now.
- Extracting the month from an order date for a report.
- Finding how long a user has been registered.
- Filtering events that occurred during a particular day.
- Recording when a payment or account update occurred.

This lesson uses PostgreSQL syntax.

Before using date and time functions, understand the main temporal types:

- DATE: a calendar date, such as 2026-10-18.
- TIME: a time of day without a date.
- TIMESTAMP: a date and time without time-zone information.
- TIMESTAMPTZ: a timestamp representing an instant, displayed according to the session time zone.
- INTERVAL: a duration or calendar-based amount of time, such as 7 days or 2 hours.

Choosing the correct type is important because a calendar date and a real-world moment are not the same thing.

2. CURRENT_DATE: Getting Today's Date

CURRENT_DATE returns the current date according to the PostgreSQL session time zone.

Syntax:

SELECT CURRENT_DATE;

It returns a DATE value without a time component.

Example:

Retrieve the Current Date

Retrieve the Current Date
sql
SELECT CURRENT_DATE AS today;
Output
today ---------- 2026-10-18

The date shown is illustrative. The actual result depends on when the query runs and the session time zone.

3. NOW() and CURRENT_TIMESTAMP

NOW() and CURRENT_TIMESTAMP return the start timestamp of the current transaction in PostgreSQL.

Examples:

SELECT NOW();
SELECT CURRENT_TIMESTAMP;

Both return a timestamp with time-zone awareness. PostgreSQL displays the value according to the session time zone.

Important distinction:
- CURRENT_DATE returns a date.
- NOW() returns a timestamp with time zone.
- CURRENT_TIMESTAMP returns the same kind of value as NOW().

NOW() is not a continuously changing clock within a transaction. Multiple calls during the same transaction return the same transaction-start timestamp.

If you specifically need the actual current clock time rather than the transaction-start time, PostgreSQL also provides clock_timestamp().

Compare Date and Timestamp

Compare Date and Timestamp
sql
SELECT
    CURRENT_DATE AS today,
    NOW() AS transaction_time,
    CURRENT_TIMESTAMP AS current_timestamp_value;
Output
Illustrative output: today | transaction_time | current_timestamp_value -----------+-----------------------------+------------------------- 2026-10-18 | 2026-10-18 10:30:00+05:30 | 2026-10-18 10:30:00+05:30

The displayed time and offset are illustrative. The two timestamp expressions represent the same transaction-start instant, while CURRENT_DATE returns only the date.

4. Understanding PostgreSQL Transaction Time

PostgreSQL provides different ways to obtain the current time.

- NOW(): transaction-start timestamp.
- CURRENT_TIMESTAMP: transaction-start timestamp.
- statement_timestamp(): start timestamp of the current statement.
- clock_timestamp(): actual clock time when the function is evaluated.

For many database operations, NOW() is useful because timestamps written during one transaction remain consistent.

Example:

BEGIN;

SELECT NOW();
SELECT pg_sleep(2);
SELECT NOW();

COMMIT;

The two NOW() calls return the same transaction-start timestamp, even though two seconds pass between them.

By contrast, clock_timestamp() can return a different value on each call.

Choose the function according to the requirement. Do not assume NOW() always means the exact wall-clock instant at the moment each expression runs.

5. EXTRACT: Getting a Date or Time Component

EXTRACT retrieves a component from a date, timestamp, time, or interval.

Syntax:

EXTRACT(field FROM source)

Common fields include:
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
- SECOND
- DOW: day of week, where Sunday is 0 and Saturday is 6 for PostgreSQL dates and timestamps.
- ISODOW: ISO day of week, where Monday is 1 and Sunday is 7.
- DOY: day of year.

Example:

Extract Date Components

Extract Date Components
sql
SELECT
    EXTRACT(YEAR FROM DATE '2026-10-18') AS year,
    EXTRACT(MONTH FROM DATE '2026-10-18') AS month,
    EXTRACT(DAY FROM DATE '2026-10-18') AS day;
Output
year | month | day -----+-------+---- 2026 | 10 | 18

EXTRACT returns a numeric result for each requested component. Depending on the PostgreSQL version and context, the result may be represented as a numeric value rather than an integer type.

6. Extracting Time Components

You can extract hours, minutes, and seconds from a timestamp or time value.

For example, the timestamp 2026-10-18 14:35:42 contains:
- Hour: 14
- Minute: 35
- Second: 42

Example:

Extract Hour, Minute, and Second

Extract Hour, Minute, and Second
sql
SELECT
    EXTRACT(HOUR FROM TIMESTAMP '2026-10-18 14:35:42') AS hour,
    EXTRACT(MINUTE FROM TIMESTAMP '2026-10-18 14:35:42') AS minute,
    EXTRACT(SECOND FROM TIMESTAMP '2026-10-18 14:35:42') AS second;
Output
hour | minute | second -----+--------+------- 14 | 35 | 42

The example uses a timestamp without time-zone information. EXTRACT reads the specified components from that timestamp value.

7. EXTRACT: Day of Week and Day of Year

PostgreSQL supports several useful calendar fields.

DOW:
- Sunday = 0
- Monday = 1
- ...
- Saturday = 6

ISODOW:
- Monday = 1
- Tuesday = 2
- ...
- Sunday = 7

DOY:
- January 1 = 1
- December 31 = 365 or 366 in a leap year

Example:

Calendar Fields

Calendar Fields
sql
SELECT
    EXTRACT(DOW FROM DATE '2026-10-18') AS day_of_week,
    EXTRACT(ISODOW FROM DATE '2026-10-18') AS iso_day_of_week,
    EXTRACT(DOY FROM DATE '2026-10-18') AS day_of_year;
Output
day_of_week | iso_day_of_week | day_of_year ------------+-----------------+------------ 0 | 7 | 291

October 18, 2026 is a Sunday and the 291st day of the year. PostgreSQL's DOW and ISODOW use different numbering conventions.

8. DATE_PART: Another Way to Extract Components

DATE_PART is another PostgreSQL function for retrieving a component from a date, timestamp, or interval.

Syntax:

DATE_PART('field', source)

Examples:

DATE_PART('year', DATE '2026-10-18')
DATE_PART('month', DATE '2026-10-18')
DATE_PART('day', DATE '2026-10-18')

EXTRACT and DATE_PART serve similar purposes. EXTRACT uses SQL-style syntax and is often clearer for beginners. DATE_PART uses a text field argument.

For new PostgreSQL code, prefer EXTRACT when its syntax makes the intended component easier to read.

EXTRACT Versus DATE_PART

EXTRACT Versus DATE_PART
sql
SELECT
    EXTRACT(YEAR FROM DATE '2026-10-18') AS year_extract,
    DATE_PART('year', DATE '2026-10-18') AS year_date_part;
Output
year_extract | year_date_part -------------+--------------- 2026 | 2026

Both expressions retrieve the year. Their return-type details can differ, so use the result type appropriate to the application.

9. INTERVAL: Representing a Duration

An INTERVAL represents an amount of time rather than a particular date or instant.

Examples:

INTERVAL '7 days'
INTERVAL '2 hours'
INTERVAL '30 minutes'
INTERVAL '3 months'
INTERVAL '1 year 2 days'

An interval can be added to or subtracted from a date or timestamp.

Examples:
- A date plus 7 days produces a date.
- A timestamp plus 2 hours produces a timestamp.
- A timestamp minus 30 minutes produces an earlier timestamp.

Calendar units such as months and years are not always equivalent to fixed numbers of seconds. For example, a month can have 28, 29, 30, or 31 days.

10. Adding an Interval to a Date

Use the plus operator to add an interval.

Syntax:

date_value + INTERVAL 'duration'

Example:

Calculate a Date Seven Days Later

Calculate a Date Seven Days Later
sql
SELECT
    DATE '2026-10-18' + INTERVAL '7 days' AS next_week;
Output
next_week --------------------- 2026-10-25 00:00:00

In PostgreSQL, adding an interval to a DATE produces a timestamp without time zone. The result represents midnight on the resulting date.

11. Subtracting an Interval

Use the minus operator to calculate an earlier date or timestamp.

For example, to calculate the date seven days before October 18, 2026:

Calculate a Previous Date

Calculate a Previous Date
sql
SELECT
    DATE '2026-10-18' - INTERVAL '7 days' AS previous_week;
Output
previous_week --------------------- 2026-10-11 00:00:00

Subtracting seven days produces the earlier date at midnight, represented as a timestamp without time zone.

12. Date Arithmetic Versus Timestamp Arithmetic

PostgreSQL supports different arithmetic depending on the input types.

| Expression | Result |
|---|---|
| DATE + INTEGER | DATE |
| DATE + INTERVAL | TIMESTAMP WITHOUT TIME ZONE |
| TIMESTAMP + INTERVAL | TIMESTAMP WITHOUT TIME ZONE |
| TIMESTAMPTZ + INTERVAL | TIMESTAMPTZ |
| DATE - DATE | INTEGER number of days |
| TIMESTAMP - TIMESTAMP | INTERVAL |

Examples:

SELECT DATE '2026-10-18' + 7;

This returns a DATE seven days later.

SELECT DATE '2026-10-18' - DATE '2026-10-11';

This returns 7, the number of days between the two dates.

These type differences matter when inserting calculated values into columns or passing them to application code.

13. Calculating a Future Deadline

Suppose a task must be completed seven days after its creation timestamp.

You can calculate the deadline directly in SQL using interval arithmetic.

Calculate a Task Deadline

Calculate a Task Deadline
sql
SELECT
    task_id,
    created_at,
    created_at + INTERVAL '7 days' AS deadline
FROM tasks;
Output
Illustrative output: task_id | created_at | deadline --------+-------------------------+------------------------- 1 | 2026-10-18 10:00:00+05:30 | 2026-10-25 10:00:00+05:30

The example assumes tasks.created_at is a timestamp column. The exact displayed offset depends on the data type and session time zone. For TIMESTAMPTZ values, calendar-day arithmetic across daylight-saving transitions can differ from adding a fixed duration of 24 hours.

14. Filtering Records by Date

Suppose an orders table contains an order_date column of type DATE.

To find orders placed on a specific date, use equality.

To find orders after a date, use a comparison operator.

Example:

Filter Orders by Date

Filter Orders by Date
sql
SELECT order_id, order_date
FROM orders
WHERE order_date = DATE '2026-10-18'
ORDER BY order_id;
Output
Illustrative output: order_id | order_date ---------+------------ 101 | 2026-10-18 105 | 2026-10-18

This query assumes order_date is a DATE column. If it is a timestamp column, equality with a date does not match every timestamp during that day.

15. Filtering Timestamp Values for a Whole Day

A timestamp stores both a date and a time. Therefore, a condition such as:

WHERE created_at = DATE '2026-10-18'

does not match every timestamp occurring on that date.

For a timestamp column, use a half-open range:

WHERE created_at >= TIMESTAMP '2026-10-18 00:00:00'
AND created_at < TIMESTAMP '2026-10-19 00:00:00'

This includes timestamps from the start of October 18 up to, but not including, the start of October 19.

Half-open ranges are preferable to guessing the final time of the day, such as 23:59:59.999999. They also work naturally with timestamp precision.

For TIMESTAMPTZ columns, define the start and end instants in the intended business time zone. Do not assume the database session time zone always matches the business's local calendar.

Select All Events on One Day

Select All Events on One Day
sql
SELECT event_id, created_at
FROM events
WHERE created_at >= TIMESTAMPTZ '2026-10-18 00:00:00+05:30'
  AND created_at <  TIMESTAMPTZ '2026-10-19 00:00:00+05:30'
ORDER BY created_at;
Output
Illustrative output: event_id | created_at ---------+--------------------------- 1 | 2026-10-18 09:15:00+05:30 2 | 2026-10-18 17:45:00+05:30

This example defines the day using the +05:30 offset. For applications with named time zones and daylight-saving rules, construct the day boundaries using the intended named time zone.

16. Finding Recent Records with NOW()

A common requirement is to retrieve records created during the last seven days.

If created_at is a timestamp column, compare it with a timestamp calculated from NOW().

Records from the Last Seven Days

Records from the Last Seven Days
sql
SELECT order_id, created_at
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
  AND created_at <= NOW()
ORDER BY created_at DESC;
Output
Illustrative output: order_id | created_at ---------+--------------------------- 203 | 2026-10-18 09:30:00+05:30 201 | 2026-10-15 14:20:00+05:30

The time window is based on the transaction-start timestamp. This is a rolling seven-day window, not the same as the previous seven calendar dates. The output is illustrative.

17. Finding Records from the Current Calendar Day

A rolling 24-hour or seven-day window differs from a calendar-day window.

To retrieve records from the current calendar day, calculate the boundaries of that day and filter the timestamp column directly.

For a TIMESTAMPTZ column, CURRENT_DATE is interpreted in the session time zone. The following pattern uses that session's calendar-day boundaries:

Records from Today

Records from Today
sql
SELECT order_id, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + 1
ORDER BY created_at;
Output
Illustrative output: order_id | created_at ---------+--------------------------- 301 | 2026-10-18 08:30:00+05:30 305 | 2026-10-18 11:45:00+05:30

For TIMESTAMPTZ values, PostgreSQL interprets the date boundaries using the session time zone. If the business day uses a different named time zone, explicitly define that time zone's boundaries instead. The output is illustrative.

18. Grouping Data by Month

EXTRACT can retrieve the month from each timestamp, but grouping only by month number combines January from different years.

For a report spanning multiple years, group by both year and month or use date_trunc to group by the month start.

Example:

Monthly Order Counts

Monthly Order Counts
sql
SELECT
    DATE_TRUNC('month', created_at) AS month_start,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month_start;
Output
Illustrative output: month_start | order_count ------------------------+------------ 2026-08-01 00:00:00+05:30 | 120 2026-09-01 00:00:00+05:30 | 145 2026-10-01 00:00:00+05:30 | 98

DATE_TRUNC groups timestamps by the start of each month, retaining the year as well. The output is illustrative, and the displayed time zone depends on the source type and session configuration.

19. Time Zones: TIMESTAMP and TIMESTAMPTZ

Time zones are important when applications operate across regions.

TIMESTAMP WITHOUT TIME ZONE:
- Stores a date and time without recording a time-zone interpretation.
- Useful when the value is intentionally a local wall-clock time, such as a store's opening time.

TIMESTAMPTZ:
- Represents an instant in time.
- PostgreSQL converts input to an instant and displays it in the current session time zone.
- It does not retain the original named time zone entered by the user.

Example:

SET TIME ZONE 'UTC';
SELECT NOW();

SET TIME ZONE 'Asia/Kolkata';
SELECT NOW();

The displayed clock time may change, but the underlying instant is the same.

For events such as payments, log entries, and completed transactions, TIMESTAMPTZ is generally a good default. For recurring appointments tied to a local time zone, you may also need to store the intended named time zone separately.

20. Adding Calendar Days Versus Fixed Hours

A day in calendar arithmetic and a fixed duration of 24 hours are not always interchangeable for TIMESTAMPTZ.

Consider:

timestamp_value + INTERVAL '1 day'

versus:

timestamp_value + INTERVAL '24 hours'

Across daylight-saving transitions, adding one calendar day can preserve local clock time while adding 24 hours advances by a fixed elapsed duration. The displayed outcomes can differ.

This distinction matters mainly when named time zones observe daylight saving.

For a requirement such as "at the same local time tomorrow," use calendar-based logic in the intended time zone. For "exactly 24 hours later," use a fixed-duration interpretation.

21. Index-Friendly Date Filtering

Suppose created_at has a regular index.

This pattern applies a function to the column:

WHERE DATE(created_at) = DATE '2026-10-18'

Depending on the query and indexes, PostgreSQL may be unable to use a regular index on created_at as efficiently as it could with a direct range condition.

Prefer a half-open range when it expresses the intended rule:

WHERE created_at >= TIMESTAMP '2026-10-18 00:00:00'
AND created_at < TIMESTAMP '2026-10-19 00:00:00'

For TIMESTAMPTZ, use boundaries representing the correct business time zone.

A regular index can support a range query, but it does not guarantee an index scan. PostgreSQL chooses a plan based on table size, statistics, selectivity, and other factors.

Use EXPLAIN to inspect the plan. Use EXPLAIN ANALYZE carefully because it actually executes the statement being analyzed.

22. Common Date and Time Expressions

| Requirement | Expression |
|---|---|
| Current date | CURRENT_DATE |
| Transaction-start timestamp | NOW() |
| Current statement-start timestamp | statement_timestamp() |
| Current clock time | clock_timestamp() |
| Extract a year | EXTRACT(YEAR FROM value) |
| Extract a month | EXTRACT(MONTH FROM value) |
| Extract an hour | EXTRACT(HOUR FROM value) |
| Extract a year using DATE_PART | DATE_PART('year', value) |
| Add seven days | value + INTERVAL '7 days' |
| Subtract two hours | value - INTERVAL '2 hours' |
| Find days between dates | date2 - date1 |
| Truncate a timestamp to month | DATE_TRUNC('month', value) |

Remember that DATE_TRUNC truncates a temporal value to a unit such as month or day. It is different from TRUNC, which operates on numeric values.

23. Practice: Predict the Output

Assume PostgreSQL and the literal dates shown in each question.

Question 1:
SELECT EXTRACT(YEAR FROM DATE '2026-10-18');

Question 2:
SELECT EXTRACT(MONTH FROM DATE '2026-10-18');

Question 3:
SELECT DATE '2026-10-18' + 7;

Question 4:
SELECT DATE '2026-10-18' - DATE '2026-10-11';

Question 5:
SELECT EXTRACT(ISODOW FROM DATE '2026-10-18');

Question 6:
SELECT TIMESTAMP '2026-10-18 10:00:00' + INTERVAL '2 hours';

Question 7:
Which condition is safer for selecting every timestamp in one day: equality to a date or a half-open range?

Question 8:
Do NOW() calls within one transaction necessarily return different timestamps as time passes?

24. Practice Answers

1. 2026 — the year component.

2. 10 — the month component.

3. DATE '2026-10-25' — adding an integer to a DATE adds that many days.

4. 7 — subtracting two DATE values returns the number of days between them.

5. 7 — October 18, 2026 is Sunday, and PostgreSQL ISODOW numbers Sunday as 7.

6. 2026-10-18 12:00:00 — two hours after 10:00.

7. A half-open range is safer for timestamp columns because it includes all timestamps from the start of the day up to, but not including, the next day.

8. No. NOW() returns the transaction-start timestamp, so it remains stable within that transaction.

25. Guided Exercise: Find Expiring Subscriptions

Scenario:

A subscriptions table contains:
- subscription_id
- expires_at, a TIMESTAMPTZ column

Task 1:
Find subscriptions expiring in the next seven days.

Task 2:
Calculate a reminder time 24 hours before each expiration.

Try writing the queries before checking the solutions.

Decide whether the range should include the exact current instant and whether the expiration boundary should be inclusive.

26. Guided Exercise Solutions

Task 1: Find upcoming expirations within the next seven days.

SELECT subscription_id, expires_at
FROM subscriptions
WHERE expires_at >= NOW()
AND expires_at < NOW() + INTERVAL '7 days'
ORDER BY expires_at;

Task 2: Calculate the reminder timestamp.

SELECT
subscription_id,
expires_at,
expires_at - INTERVAL '24 hours' AS reminder_at
FROM subscriptions
ORDER BY expires_at;

Explanation:
The first query selects expiration timestamps from the current transaction time up to, but not including, seven days later. The second calculates a reminder instant 24 hours before expiration.

For production reminder systems, also decide how to handle already-expired subscriptions, time-zone requirements, and repeated notification prevention.

27. Multiple-Choice Quiz

Choose one answer for each question. Correct answers and explanations follow each question.

28. Interview Questions and Answers

Q1. What are date and time functions in SQL?
Answer: Functions and expressions used to retrieve, extract, compare, and calculate date and time values.

Q2. What is CURRENT_DATE?
Answer: It returns the current date according to the PostgreSQL session time zone.

Q3. What is NOW()?
Answer: It returns the start timestamp of the current transaction.

Q4. What is the difference between NOW() and clock_timestamp()?
Answer: NOW() is stable within a transaction, while clock_timestamp() reports the actual clock time when evaluated.

Q5. What does CURRENT_TIMESTAMP return?
Answer: A timestamp with time zone representing the transaction-start time in PostgreSQL.

Q6. What is EXTRACT used for?
Answer: It retrieves a component such as year, month, day, hour, or minute from a temporal value.

Q7. What is the difference between EXTRACT and DATE_PART?
Answer: Both can extract date and time components in PostgreSQL, but EXTRACT uses SQL-style syntax while DATE_PART takes the field as text.

Q8. What is an INTERVAL?
Answer: A temporal duration or calendar-based amount such as seven days or two hours.

Q9. How do you add seven days to a DATE?
Answer: Use date_value + 7 or date_value + INTERVAL '7 days'. The result types differ.

Q10. What is the result of subtracting one DATE from another?
Answer: PostgreSQL returns the integer number of days between them.

Q11. What is the difference between DATE and TIMESTAMP?
Answer: DATE stores a calendar date only; TIMESTAMP stores a date and time without time-zone information.

Q12. What is TIMESTAMPTZ?
Answer: A PostgreSQL type representing an instant in time, displayed according to the session time zone.

Q13. Does TIMESTAMPTZ preserve the original time-zone name?
Answer: No. It stores the instant, not the original named time zone. Store that name separately if the application needs it.

Q14. Why can equality to a date fail when filtering timestamps?
Answer: A timestamp includes a time component, so equality does not match all timestamps during that calendar day.

Q15. What is a half-open date range?
Answer: A range that includes its start but excludes its end, commonly written as column >= start AND column < next_start.

Q16. Why are half-open ranges useful?
Answer: They handle timestamp precision cleanly and avoid guessing the final instant of a day.

Q17. How can you find records from the previous seven days?
Answer: Compare the timestamp to NOW() - INTERVAL '7 days', with explicit boundaries based on the intended requirement.

Q18. Is the previous seven days the same as the previous seven calendar dates?
Answer: No. A rolling duration and a calendar-day range have different boundaries.

Q19. What does DATE_TRUNC do?
Answer: It truncates a temporal value to a unit such as year, month, day, or hour.

Q20. Why can wrapping a timestamp column in a function affect index usage?
Answer: A normal index on the original column may not support the function expression as directly as a range condition. The actual plan depends on the query and indexes.

Q21. Why should time-zone requirements be specified in applications?
Answer: The session time zone may differ from the business's local time zone, changing calendar-day boundaries and displayed timestamps.

Q22. Are adding one day and adding 24 hours always equivalent?
Answer: Not for every TIMESTAMPTZ operation across daylight-saving transitions. Calendar arithmetic and fixed-duration arithmetic can differ.

Q23. How do you extract the month from a timestamp?
Answer: Use EXTRACT(MONTH FROM timestamp_value) or DATE_PART('month', timestamp_value).

Q24. Why should reports group by year and month instead of month number alone?
Answer: Grouping only by month number can combine data from different years.

Q25. What should you check before deploying date-based queries?
Answer: Column types, time zones, boundary inclusivity, transaction-time semantics, calendar versus rolling windows, and query plans where performance matters.

29. Lesson Summary

In this lesson, you learned:

- CURRENT_DATE retrieves the current date.
- NOW() and CURRENT_TIMESTAMP return the transaction-start timestamp.
- clock_timestamp() returns the actual current clock time when evaluated.
- EXTRACT and DATE_PART retrieve date and time components.
- INTERVAL represents a duration or calendar-based amount.
- PostgreSQL supports date and timestamp arithmetic.
- Subtracting two DATE values returns the number of days between them.
- Half-open ranges are useful for timestamp filtering.
- TIMESTAMPTZ represents an instant and is displayed according to the session time zone.
- Calendar days and fixed durations can differ around daylight-saving transitions.
- DATE_TRUNC is useful for grouping dates and timestamps by a calendar unit.

Before writing a date query, ask:
1. Do I have a DATE, TIMESTAMP, or TIMESTAMPTZ column?
2. Do I need a rolling duration or a calendar-day range?
3. Which time zone defines the intended day?
4. Are the start and end boundaries inclusive or exclusive?

30. Common Mistakes

Avoid these frequent mistakes when working with PostgreSQL date and time values.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • CURRENT_DATE returns the current date in the session time zone.
  • NOW() and CURRENT_TIMESTAMP return the start time of the current transaction.
  • EXTRACT retrieves a component such as year, month, or day from a temporal value.
  • DATE_PART provides another way to extract date and time components.
  • INTERVAL represents a duration such as seven days or two hours.
  • Adding an interval to a date or timestamp produces another date or timestamp.
  • Use half-open date ranges for reliable timestamp filtering.
  • Use TIMESTAMPTZ for timestamps representing real-world instants and understand session time zone conversion.
  • Avoid wrapping an indexed timestamp column in a function when a direct range condition can express the same requirement.
Was this lesson helpful?
Found an error? Report an issue