How often do you see apps that deal with dates and time? Online orders, schedules, change history in a database — all of them depend on storing time the right way. PostgreSQL gives you some powerful tools for handling this kind of data, and, of course, time tables aren’t scary tables with clocks and minutes, but a clear structure that lets you analyze and organize events.
Date and time types let you:
- Store just the date with
DATE, just the time withTIME, or both together withTIMESTAMP. - Do stuff like add days, calculate the difference between dates, and filter records by time criteria.
- Work with time zones (but that’s a topic for another lecture).
Data types: DATE, TIME, TIMESTAMP
DATE data type
The DATE type is for storing just the date, no time. It saves the year, month, and day. This type is handy when:
- You need to store a user’s birthday.
- The date of an event matters, but the time doesn’t.
Example format: YYYY-MM-DD (year-month-day).
Example:
| id | name - VARCHAR(100) | event_date - DATE |
|---|---|---|
| 1 | SQL Workshop | 2025-06-15 |
| 2 | Python | 2025-06-17 |
| 3 | Java Courses | 2025-06-25 |
TIME data type
The TIME type is used to store just the time. It’s useful when:
- You need to save a schedule, like the start of the workday.
- The time matters, but the date doesn’t.
Example format: HH:MI:SS (hours:minutes:seconds).
Example:
| id | task_name - VARCHAR(100) | start_time - TIME |
|---|---|---|
| 1 | Team Meeting | 09:00:00 |
| 2 | Code Review | 11:30:00 |
| 3 | Client Call | 15:00:00 |
TIMESTAMP data type
TIMESTAMP is a combo of date and time. It’s useful when:
- You need to record a moment in time, like when a user logged in.
- You need a time mark for tracking changes (logging).
Example format: YYYY-MM-DD HH:MI:SS (year-month-day hours:minutes:seconds).
Example:
| id | action - VARCHAR(100) | login_time - TIMESTAMP |
|---|---|---|
| 1 | User Login | 2023-10-15 14:30:00 |
| 2 | File Uploaded | 2023-10-15 15:10:00 |
| 3 | User Logout | 2023-10-15 16:45:00 |
Working with dates and times
Now that we know what types exist, let’s figure out how to use them. PostgreSQL gives you a bunch of built-in functions for working with dates and times.
Extracting parts of date and time
If you have a timestamp (TIMESTAMP) and want to pull out just the year, month, day, or hour, use the EXTRACT function.
Example:
SELECT EXTRACT(YEAR FROM TIMESTAMP '2025-06-15 14:30:00') AS year;
-- Result: 2025
SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-06-15 14:30:00') AS month;
-- Result: 06
SELECT EXTRACT(DAY FROM TIMESTAMP '2025-06-15 14:30:00') AS day;
-- Result: 15Adding and subtracting time intervals
Want to know what happens a week from now? Or what was yesterday? Use interval operations.
Example:
-- Adding 7 days to the current date
SELECT CURRENT_DATE + INTERVAL '7 days' AS next_week;
-- Subtracting 1 month
SELECT CURRENT_DATE - INTERVAL '1 month' AS last_month;Comparing dates
How do you check if an event already happened? Easier than finding a bug in your code — just compare the values.
Example:
SELECT event_date
FROM events
WHERE event_date < CURRENT_DATE;
-- Select all events in the past
You’ll learn more about date and time functions in the next lectures. For now, just remember these types exist — that’s enough for a start.
Time zone issues and time standard
Even though we’re not diving into time zones in this lecture, it’s important to mention that PostgreSQL supports the TIMESTAMPTZ type (timestamp with time zone). For example, 2023-10-15 14:30:00+02 shows that this time is in the UTC+2 time zone.
We’ll definitely cover this topic in our course, just a bit later :P
Example
Now it’s time to check out our new skills in practice. Let’s create a table that stores students’ class schedules.
| id | subject_name | class_date - DATE | start_time - TIME | end_time - TIME | created_at - TIMESTAMP |
|---|---|---|---|---|---|
| 1 | Mathematics | 2023-10-16 | 09:00:00 | 10:30:00 | 2023-10-12 14:00:00 |
| 2 | Physics | 2023-10-16 | 11:00:00 | 12:30:00 | 2023-10-12 14:00:00 |
| 3 | Chemistry | 2023-10-17 | 09:00:00 | 10:30:00 | 2023-10-12 14:01:00 |
| 4 | Literature | 2023-10-17 | 11:00:00 | 12:30:00 | 2023-10-12 14:01:00 |
| 5 | Computer Science | 2023-10-18 | 10:00:00 | 11:30:00 | 2023-10-12 14:02:00 |
In a couple lectures, you’ll be creating tables like this in your database using SQL queries. For now, just enjoy the view :)
Common mistakes
Date and time format: when inserting data, it’s important to use the right format: YYYY-MM-DD for dates and HH:MI:SS for time. PostgreSQL might not get it if you try to insert a date like "15/10/2023".
Data type mismatch: trying to put a text value into a DATE field will throw an error.
Interval mistakes: if you add, say, 30 days to February, PostgreSQL will handle it, but the date might end up in March.
Now you know the data types for working with date and time in PostgreSQL. In real projects, this helps you handle schedules, log events, or track important stuff in your system!
GO TO FULL VERSION