Date, time, and timestamp types in PostgreSQL - Deep Dive

Overview - Date, time, and timestamp types

What is it?

Date, time, and timestamp types in PostgreSQL are special ways to store information about dates and times. They let you save just a date, just a time, or both date and time together. These types help computers understand and work with calendar dates and clock times correctly. They also support time zones and fractions of seconds for precise timing.

Why it matters

Without these types, storing dates and times would be confusing and error-prone, like writing dates as plain text. Calculations like finding the difference between two dates or sorting events by time would be hard and unreliable. These types make it easy to handle scheduling, logging, and time-based data accurately in databases, which is essential for many real-world applications like calendars, bookings, and data analysis.

Where it fits

Before learning this, you should understand basic data types like integers and strings in databases. After this, you can learn about date and time functions, intervals, and how to use these types in queries and applications. This topic fits into the broader study of data modeling and querying in relational databases.

Mental Model

Core Idea

Date, time, and timestamp types are structured formats that let databases store and manipulate calendar dates and clock times precisely and consistently.

Think of it like...

Think of these types like different kinds of clocks and calendars: a calendar shows just the date, a clock shows just the time, and a smartwatch shows both date and time together with extra details like time zones.

┌───────────────┐   ┌───────────────┐   ┌─────────────────────┐
│    DATE       │   │    TIME       │   │     TIMESTAMP       │
│ (YYYY-MM-DD)  │   │ (HH:MM:SS)    │   │ (YYYY-MM-DD HH:MM:SS)│
│               │   │               │   │                     │
│ Only date     │   │ Only time     │   │ Date and time       │
│ no time zone  │   │ no date       │   │ with optional zone  │
└───────────────┘   └───────────────┘   └─────────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding the DATE type

Concept: Learn what the DATE type stores and how it formats dates.

The DATE type stores calendar dates in the format YYYY-MM-DD, like 2024-06-15. It does not store time or time zone information. You can use it to represent birthdays, holidays, or any event that happens on a specific day.

Result

You can store and retrieve dates accurately, for example, '2024-06-15'.

Knowing that DATE stores only the day part helps avoid confusion when you only need the calendar date without time.

2

Foundation

Exploring the TIME type

Concept: Understand how the TIME type stores clock times without dates.

The TIME type stores time of day in the format HH:MM:SS, like 14:30:00 for 2:30 PM. It does not include a date or time zone. This is useful for representing daily schedules or opening hours.

Result

You can store times like '14:30:00' and use them independently of dates.

Separating time from date allows flexible use when only the time of day matters.

3

Intermediate

Using TIMESTAMP for date and time

🤔Before reading on: do you think TIMESTAMP stores time zone information by default? Commit to your answer.

Concept: TIMESTAMP stores both date and time together, optionally with time zone info.

TIMESTAMP combines date and time in one value, like '2024-06-15 14:30:00'. PostgreSQL has two types: TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE (called TIMESTAMPTZ). The latter adjusts times to UTC internally and converts on display based on time zone settings.

Result

You can store precise moments in time, with or without time zone awareness.

Understanding the difference between TIMESTAMP types prevents bugs in applications that work across time zones.

4

Intermediate

Fractional seconds and precision

🤔Before reading on: do you think PostgreSQL stores time with millisecond precision by default? Commit to your answer.

Concept: PostgreSQL supports fractional seconds in time and timestamp types with configurable precision.

You can store fractions of a second, like '14:30:00.123456', where the number after the decimal shows microseconds. The precision can be set from 0 to 6 digits. This is important for logging events that happen very close in time.

Result

You get very precise time values, useful for detailed event tracking.

Knowing about fractional seconds helps when exact timing matters, such as in financial or scientific data.

5

Advanced

Time zones and TIMESTAMPTZ behavior

🤔Before reading on: do you think TIMESTAMPTZ stores the original time zone you input? Commit to your answer.

Concept: TIMESTAMPTZ stores times in UTC internally and converts to local time on output.

When you insert a TIMESTAMPTZ value with a time zone, PostgreSQL converts it to UTC for storage. When you query it, PostgreSQL converts it back to your session's time zone. This means the stored value is always in UTC, ensuring consistency across systems.

Result

You get consistent, comparable timestamps regardless of user location or server settings.

Understanding this conversion avoids confusion about what time is actually stored and displayed.

6

Expert

Performance and storage considerations

🤔Before reading on: do you think TIMESTAMP WITH TIME ZONE uses more storage than TIMESTAMP WITHOUT TIME ZONE? Commit to your answer.

Concept: Different date/time types have different storage sizes and performance impacts.

DATE uses 4 bytes, TIME uses 8 bytes, TIMESTAMP WITHOUT TIME ZONE uses 8 bytes, and TIMESTAMPTZ also uses 8 bytes. However, TIMESTAMPTZ requires extra processing for time zone conversions. Choosing the right type affects storage size and query speed, especially in large datasets.

Result

You can optimize database size and performance by selecting appropriate types.

Knowing storage and processing costs helps design efficient databases for time-related data.

Under the Hood

PostgreSQL stores DATE as a 4-byte integer counting days since a fixed date (2000-01-01). TIME and TIMESTAMP types store time as microseconds since midnight or since the epoch (2000-01-01 00:00:00). TIMESTAMPTZ converts input times to UTC internally and stores them as TIMESTAMP WITHOUT TIME ZONE values, applying time zone offsets only when displaying data.

Why designed this way?

This design balances storage efficiency and precision. Using integers and microseconds allows fast arithmetic and comparisons. Storing TIMESTAMPTZ in UTC avoids ambiguity and errors from daylight saving changes or multiple time zones. Alternatives like storing raw strings would be slower and error-prone.

┌───────────────┐       ┌───────────────┐       ┌─────────────────────┐
│   DATE (4B)   │       │ TIME (8B)     │       │ TIMESTAMP (8B)      │
│ Days since    │       │ Microseconds  │       │ Microseconds since  │
│ 2000-01-01    │       │ since midnight│       │ 2000-01-01 00:00:00 │
└──────┬────────┘       └──────┬────────┘       └─────────┬───────────┘
       │                       │                          │
       │                       │                          │
       │                       │                          │
       │                       │                          │
       │                       │                          │
       │                       │                          │
       ▼                       ▼                          ▼
┌───────────────────────────────────────────────────────────────────────┐
│                       TIMESTAMPTZ STORAGE                            │
│                                                                       │
│ Input with time zone → Convert to UTC → Store as TIMESTAMP WITHOUT TZ │
│                                                                       │
│ On query → Convert UTC to session time zone → Display                  │
└───────────────────────────────────────────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does TIMESTAMP WITH TIME ZONE store the original time zone you input? Commit to yes or no.

Common Belief: TIMESTAMP WITH TIME ZONE stores the exact time zone information you provide.

Reality: It converts the time to UTC internally and does not store the original time zone offset.

Why it matters: Assuming the original time zone is stored can cause errors when displaying or comparing times across zones.

Quick: Is TIME type aware of dates or time zones? Commit to yes or no.

Common Belief: TIME type includes date and time zone information.

Reality: TIME stores only the time of day without any date or time zone context.

Why it matters: Using TIME for full timestamps leads to incomplete data and incorrect calculations.

Quick: Does DATE type store time or time zone? Commit to yes or no.

Common Belief: DATE stores date and time zone information.

Reality: DATE stores only the calendar date without time or time zone.

Why it matters: Mixing DATE with time or zone assumptions causes bugs in scheduling and comparisons.

Quick: Does fractional seconds precision default to microseconds? Commit to yes or no.

Common Belief: PostgreSQL stores fractional seconds with microsecond precision by default.

Reality: The default precision is 6 digits (microseconds), but it can be configured or truncated.

Why it matters: Not knowing precision settings can cause unexpected rounding or data loss.

Expert Zone

1

TIMESTAMPTZ does not store the original time zone, only the UTC equivalent, so applications needing original zone info must store it separately.

2

Using TIMESTAMP WITHOUT TIME ZONE for local times avoids unexpected conversions but requires careful handling when comparing across zones.

3

Fractional seconds precision affects index size and query performance; choosing the right precision balances accuracy and efficiency.

When NOT to use

Avoid TIMESTAMPTZ when you need to store local times without conversion, such as store opening hours or scheduled times that do not shift with time zones. Use TIME or TIMESTAMP WITHOUT TIME ZONE instead. For durations or intervals, use the INTERVAL type rather than TIMESTAMP.

Production Patterns

In production, TIMESTAMPTZ is commonly used for logging events with exact moments in UTC. DATE is used for birthdays or holidays. TIME is used for daily schedules. Applications often convert TIMESTAMPTZ to user time zones in the application layer. Indexes on TIMESTAMPTZ columns optimize time-based queries.

Connections

Unix Epoch Time

builds-on

Understanding how PostgreSQL counts time from a fixed point (epoch) helps grasp how timestamps are stored and compared.

Time Zones in Operating Systems

same pattern

Both PostgreSQL and OS handle time zones by converting local times to UTC internally, ensuring consistent time representation.

Physics: Time Measurement

builds-on

Knowing how precise time measurement in physics uses fractions of seconds helps appreciate why databases support microsecond precision.

Common Pitfalls

#1 Storing local times with TIMESTAMPTZ without understanding time zone conversion.

Wrong approach:

INSERT INTO events (start_time) VALUES ('2024-06-15 14:00:00+05'); -- expects stored as local time

Correct approach:

INSERT INTO events (start_time) VALUES (TIMESTAMP WITHOUT TIME ZONE '2024-06-15 14:00:00'); -- stores local time without conversion

Root cause:

Misunderstanding that TIMESTAMPTZ converts input to UTC, changing the stored time.

#2 Using DATE type to store timestamps with time and time zone.

Wrong approach:

INSERT INTO logs (event_date) VALUES ('2024-06-15 14:30:00+02');

Correct approach:

INSERT INTO logs (event_timestamp) VALUES (TIMESTAMPTZ '2024-06-15 14:30:00+02');

Root cause:

Confusing DATE with TIMESTAMP types and their capabilities.

#3 Ignoring fractional seconds precision and losing data.

Wrong approach:

CREATE TABLE measurements (time TIME(0)); INSERT INTO measurements VALUES ('14:30:00.123456');

Correct approach:

CREATE TABLE measurements (time TIME(6)); INSERT INTO measurements VALUES ('14:30:00.123456');

Root cause:

Not setting sufficient precision for fractional seconds.

Key Takeaways

Date, time, and timestamp types let databases store calendar and clock information precisely and consistently.

DATE stores only the date, TIME stores only the time, and TIMESTAMP stores both date and time, optionally with time zone.

TIMESTAMPTZ stores times in UTC internally and converts to local time on output, avoiding time zone confusion.

Fractional seconds precision allows microsecond accuracy, important for detailed event timing.

Choosing the right type and understanding their behavior prevents bugs and improves database performance.

Practice

1. Which PostgreSQL data type should you use to store only the date without any time information?

easy

A. TIMESTAMP

B. TIME

C. INTERVAL

D. DATE

Solution

1. Step 1: Understand the data types

   DATE stores only the calendar date (year, month, day) without time.

2. Step 2: Match the requirement

   Since we want only the date without time, DATE is the correct choice.

3. Final Answer:

   DATE -> Option D

4. Quick Check:

   Date only = DATE [OK]

Hint: Use DATE for day only, no time needed [OK]

Common Mistakes:

• Choosing TIMESTAMP which includes time
• Using TIME which stores only time
• Confusing INTERVAL with date storage

2. Which of the following is the correct way to declare a column named created_at that stores date and time with time zone in PostgreSQL?

easy

A. created_at TIMESTAMP WITH TIME ZONE

B. created_at TIMESTAMP

C. created_at DATE WITH TIME ZONE

D. created_at TIME WITH TIME ZONE

Solution

1. Step 1: Identify the correct type for date and time with time zone

   PostgreSQL uses TIMESTAMP WITH TIME ZONE to store both date and time including time zone info.

2. Step 2: Check the options

   created_at TIMESTAMP WITH TIME ZONE matches the correct syntax and type. The other options are either missing time zone or invalid.

3. Final Answer:

   created_at TIMESTAMP WITH TIME ZONE -> Option A

4. Quick Check:

   Date & time + time zone = TIMESTAMP WITH TIME ZONE [OK]

Hint: Use TIMESTAMP WITH TIME ZONE for date, time, and zone [OK]

Common Mistakes:

• Using TIMESTAMP without time zone when zone is needed
• Trying to add time zone to DATE or TIME types
• Incorrect syntax like DATE WITH TIME ZONE

3. Given the table events(event_time TIMESTAMP) and the query:
SELECT event_time::DATE FROM events WHERE event_time > '2024-01-01 12:00:00';
What will be the output type of the query?

medium

A. TIMESTAMP values

B. TIME values

C. DATE values

D. TEXT values

Solution

1. Step 1: Understand the cast operation

   The ::DATE casts the TIMESTAMP to DATE, removing time part.

2. Step 2: Analyze the query output

   The SELECT returns only the date part of event_time, so output type is DATE.

3. Final Answer:

   DATE values -> Option C

4. Quick Check:

   CAST TIMESTAMP to DATE = DATE output [OK]

Hint: Casting TIMESTAMP::DATE returns only date part [OK]

Common Mistakes:

• Assuming output remains TIMESTAMP
• Thinking output is TIME only
• Confusing cast with string conversion

4. You wrote this query:
SELECT event_time + '1 day' FROM events;
But it gives an error. What is the likely cause?

medium

A. The string '1 day' must be cast to INTERVAL before adding

B. You cannot add intervals to TIMESTAMP in PostgreSQL

C. You must use DATE type for adding days

D. The column event_time is not a date or timestamp

Solution

1. Step 1: Understand adding intervals to timestamps

   In PostgreSQL, you add intervals to timestamps, but the interval must be of type INTERVAL.

2. Step 2: Identify the error cause

   The string '1 day' is text and must be cast to INTERVAL using '1 day'::INTERVAL or INTERVAL '1 day'.

3. Final Answer:

   The string '1 day' must be cast to INTERVAL before adding -> Option A

4. Quick Check:

   Add INTERVAL to TIMESTAMP, cast string first [OK]

Hint: Cast '1 day' to INTERVAL before adding to timestamp [OK]

Common Mistakes:

• Adding string directly without casting
• Assuming TIMESTAMP + string works
• Using DATE type incorrectly

5. You want to store event start times in UTC but display them in users' local time zones. Which PostgreSQL data type and function combination is best to store and convert these times?

hard

A. Store as TIMESTAMP and use AT TIME ZONE to convert

B. Store as TIMESTAMP WITH TIME ZONE and use AT TIME ZONE to convert

C. Store as TIME and use SET TIMEZONE to convert

D. Store as DATE and use TIMEZONE() to convert

Solution

1. Step 1: Choose the correct storage type

   TIMESTAMP WITH TIME ZONE stores the exact moment in time with zone info, ideal for UTC storage.

2. Step 2: Use conversion function

   AT TIME ZONE converts stored UTC times to users' local time zones for display.

3. Final Answer:

   Store as TIMESTAMP WITH TIME ZONE and use AT TIME ZONE to convert -> Option B

4. Quick Check:

   Use TIMESTAMP WITH TIME ZONE + AT TIME ZONE for zone conversion [OK]

Hint: Use TIMESTAMP WITH TIME ZONE + AT TIME ZONE for conversions [OK]

Common Mistakes:

• Storing as TIMESTAMP without time zone loses zone info
• Using DATE or TIME types which lack full timestamp info
• Confusing SET TIMEZONE session setting with conversion