Date, time, and timestamp types in PostgreSQL - Step-by-Step Execution

Concept Flow - Date, time, and timestamp types

Start: Input Date/Time Value

↓

Check Data Type

↓

Date

↓

Store in DB

↓

Retrieve & Format

↓

Output

The flow shows how input values are checked for type (date, time, timestamp), stored, and then retrieved formatted.

Execution Sample

PostgreSQL

CREATE TABLE events (
  event_date DATE,
  event_time TIME,
  event_timestamp TIMESTAMP
);

INSERT INTO events VALUES ('2024-06-01', '14:30:00', '2024-06-01 14:30:00');

SELECT * FROM events;

This code creates a table with date, time, and timestamp columns, inserts one row, and selects all rows.

Execution Table

Step	Action	Input Value	Data Type Detected	Stored Value	Output on SELECT
1	Create table with date, time, timestamp columns	N/A	N/A	Table schema created	N/A
2	Insert row with '2024-06-01', '14:30:00', '2024-06-01 14:30:00'	2024-06-01, 14:30:00, 2024-06-01 14:30:00	Date, Time, Timestamp	Stored as date, time, timestamp	N/A
3	Select all rows from events	N/A	N/A	N/A	event_date: 2024-06-01, event_time: 14:30:00, event_timestamp: 2024-06-01 14:30:00
4	End of execution	N/A	N/A	N/A	Query complete, all data retrieved

💡 All rows processed and output displayed

Variable Tracker

Variable	Start	After Insert	After Select	Final
event_date	NULL	2024-06-01	2024-06-01	2024-06-01
event_time	NULL	14:30:00	14:30:00	14:30:00
event_timestamp	NULL	2024-06-01 14:30:00	2024-06-01 14:30:00	2024-06-01 14:30:00

Key Moments - 3 Insights

Why does '2024-06-01' store differently in DATE and TIMESTAMP columns?

DATE stores only the date part without time, while TIMESTAMP stores both date and time. See execution_table row 2 where data types differ.

What happens if you insert a time value into a DATE column?

PostgreSQL will reject it or truncate time part because DATE only accepts date values. This is why data types must match as shown in row 2.

Why does SELECT show the full timestamp including time?

Because TIMESTAMP stores both date and time, SELECT outputs the full value as in row 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the stored value for event_time after the insert step?

A 2024-06-01 14:30:00

B 2024-06-01

C 14:30:00

D NULL

💡 Hint

Check the 'Stored Value' column in execution_table row 2 for event_time

At which step does the SELECT query output the full timestamp?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Output on SELECT' column in execution_table row 3

If you tried to insert '14:30:00' into event_date, what would happen?

A It stores as '2024-06-01' in event_date

B Insertion fails due to type mismatch

C It stores as '14:30:00' in event_date

D It stores as NULL

💡 Hint

Refer to key_moments about type mismatch on insert

Concept Snapshot

PostgreSQL date/time types:
- DATE stores only date (YYYY-MM-DD)
- TIME stores only time (HH:MM:SS)
- TIMESTAMP stores date and time (YYYY-MM-DD HH:MM:SS)
Insert values must match column type
SELECT returns stored values formatted accordingly

Full Transcript

This visual execution shows how PostgreSQL handles date, time, and timestamp types. First, a table is created with columns for each type. Then, a row is inserted with matching values: a date string for DATE, a time string for TIME, and a combined date-time string for TIMESTAMP. The execution table traces each step: creation, insertion, and selection. Variables track how values change from NULL to stored data. Key moments clarify common confusions like why date and timestamp differ and what happens on type mismatch. The quiz tests understanding of stored values and behavior on insert and select. The snapshot summarizes the core rules for these types in PostgreSQL.

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