PostgreSQL Date and Time for Beginners: NOW() and INTERVAL
PostgreSQL date and time for beginners: learn DATE, TIMESTAMP, NOW(), INTERVAL and filtering by date with simple, copy-paste examples you can run today.
PostgreSQL date and time for beginners: learn DATE, TIMESTAMP, NOW(), INTERVAL and filtering by date with simple, copy-paste examples you can run today.
How do you work with dates and times in PostgreSQL? You store them in columns of type DATE or TIMESTAMP, get the current moment with NOW(), do date maths with INTERVAL, and filter rows with normal WHERE comparisons. That is the whole idea, and in this guide you will learn each piece step by step with small examples you can paste into psql.
PostgreSQL is a free database that stores data in tables. A table is like a spreadsheet: it has columns (the headings) and rows (the records). If you are new to tables, read our guide on creating tables first. No other knowledge is needed here.
You could store a date as plain text, like '2026-11-18'. But then the database cannot tell that November 18 comes before November 20, and it cannot add 7 days to it. A real date type lets PostgreSQL sort, compare and calculate with dates correctly.
A data type tells PostgreSQL what kind of value a column holds. These are the date and time types you will use most:
| Type | What it stores | Example value |
|---|---|---|
| DATE | A calendar day only | 2026-11-18 |
| TIME | A time of day only | 14:30:00 |
| TIMESTAMP | A date and a time, no time zone | 2026-11-18 14:30:00 |
| TIMESTAMPTZ | A date and time that knows its time zone | 2026-11-18 14:30:00+00 |
| INTERVAL | A length of time | 3 days |
As a beginner, use DATE when you only care about the day (a birthday) and TIMESTAMPTZ when you care about the exact moment (when an order was placed). TIMESTAMPTZ is short for timestamp with time zone, and it avoids confusing bugs when users live in different countries.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL,
order_date DATE,
created_at TIMESTAMPTZ DEFAULT NOW()
);Line 1 creates a table named orders. Line 2 adds an id that counts up by itself. Line 3 stores the customer name. Line 4 adds order_date, a plain DATE. Line 5 adds created_at, a TIMESTAMPTZ whose DEFAULT is NOW(), which means PostgreSQL fills in the current time automatically if you do not provide one.
Write dates as text in the year-month-day format, wrapped in single quotes. This format is clear everywhere in the world.
INSERT INTO orders (customer, order_date)
VALUES
('Anna', '2026-11-01'),
('Ben', '2026-11-10'),
('Chloe', '2026-11-17');This adds three rows. We did not set created_at, so PostgreSQL used NOW() for it. The dates are in quotes, but PostgreSQL turns them into real DATE values because the column is a DATE.
SELECT NOW();
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;NOW() returns the current date and time. CURRENT_DATE returns only today's date. CURRENT_TIME returns only the time. You can run each one on its own, without any table, to see what it gives you.
An INTERVAL is a span of time, such as '7 days' or '2 hours'. You can add it to or subtract it from a date.
SELECT NOW() + INTERVAL '7 days' AS next_week;
SELECT NOW() - INTERVAL '1 month' AS last_month;
SELECT CURRENT_DATE + INTERVAL '2 hours' AS plus_two_hours;The first line gives the moment 7 days from now. The second gives the moment one month ago. The third adds 2 hours to today's date. The word AS gives the result column a friendly name. Units you can use include seconds, minutes, hours, days, weeks, months and years.
Dates can be compared with the same signs you use for numbers: =, >, <, >= and <=. WHERE is the part of a query that picks which rows to return.
SELECT * FROM orders
WHERE order_date >= '2026-11-05';This returns every order placed on or after November 5, 2026. Anna's order on November 1 is left out.
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';This shows orders from the last 30 days, whatever today's date is. Mixing CURRENT_DATE with INTERVAL is the most common way to build a "recent items" list. You can also use BETWEEN to choose a range, for example WHERE order_date BETWEEN '2026-11-01' AND '2026-11-15'.
Sometimes you want just the year, month or day. The EXTRACT function pulls one part out of a date.
SELECT
customer,
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month
FROM orders;For each order, this shows the customer plus the year and month of the order. Inside EXTRACT you write the part you want (YEAR, MONTH, DAY, HOUR and so on), then FROM, then the column.
SELECT TO_CHAR(order_date, 'DD Mon YYYY') AS pretty_date
FROM orders;TO_CHAR turns a date into text in the shape you choose. Here DD is the day, Mon is the short month name and YYYY is the four-digit year, so 2026-11-10 becomes 10 Nov 2026. Remember the result is text, so keep the original column for sorting and comparing.
New to filtering rows? Our beginner guide to the PostgreSQL WHERE clause shows every comparison you can use with dates.
Read the WHERE clause guideDATE stores only the calendar day, such as 2026-11-18. TIMESTAMP stores the day and the time of day. Use DATE for things like birthdays and TIMESTAMP when the exact time matters.
Run SELECT CURRENT_DATE; to get today's date only. Run SELECT NOW(); to get the current date and time together.
Add an INTERVAL to the date. For example, SELECT CURRENT_DATE + INTERVAL '7 days'; returns the date one week from today. Use a minus sign to go backwards.
TIMESTAMP ignores time zones, while TIMESTAMPTZ stores the moment in a way that is correct for every time zone. Most beginners should pick TIMESTAMPTZ to avoid confusing time bugs.
PostgreSQL accepts the clear YYYY-MM-DD format, like 2026-11-18, and this is the safest one to write. You can show dates in other styles later using the TO_CHAR function.
Want help building a database-powered app or learning with real projects? Get in touch with the Fepiq team.
Contact FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.