All posts
PostgreSQLNovember 18, 20268 min read

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.

F
Fepiq Team
Fepiq

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.

Why dates and times need special types

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.

The main date and time data types

A data type tells PostgreSQL what kind of value a column holds. These are the date and time types you will use most:

TypeWhat it storesExample value
DATEA calendar day only2026-11-18
TIMEA time of day only14:30:00
TIMESTAMPA date and a time, no time zone2026-11-18 14:30:00
TIMESTAMPTZA date and time that knows its time zone2026-11-18 14:30:00+00
INTERVALA length of time3 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 a table with date columns

sql
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.

Insert dates into a table

Write dates as text in the year-month-day format, wrapped in single quotes. This format is clear everywhere in the world.

sql
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.

Get the current date and time

sql
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.

Add and subtract time with INTERVAL

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.

sql
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.

Filter rows by date with WHERE

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.

sql
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.

sql
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'.

Pick out part of a date with EXTRACT

Sometimes you want just the year, month or day. The EXTRACT function pulls one part out of a date.

sql
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.

Format a date for display with TO_CHAR

sql
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.

Common beginner mistakes

  • Storing dates as plain text. Use DATE or TIMESTAMPTZ so sorting and maths work.
  • Forgetting the single quotes around a date value. Write '2026-11-18', not 2026-11-18.
  • Using double quotes for text or dates. In PostgreSQL, double quotes are for names, single quotes are for values.
  • Comparing a TIMESTAMP to a plain date and being surprised. '2026-11-18' means midnight at the start of that day, so rows later that day may not match an equals check.
  • Ignoring time zones. When in doubt, choose TIMESTAMPTZ.

New to filtering rows? Our beginner guide to the PostgreSQL WHERE clause shows every comparison you can use with dates.

Read the WHERE clause guide

Frequently asked questions

What is the difference between DATE and TIMESTAMP in PostgreSQL?+

DATE 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.

How do I get today's date in PostgreSQL?+

Run SELECT CURRENT_DATE; to get today's date only. Run SELECT NOW(); to get the current date and time together.

How do I add days to a date in PostgreSQL?+

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.

What is the difference between TIMESTAMP and TIMESTAMPTZ?+

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.

What date format does PostgreSQL use?+

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 Fepiq

Get new posts in your inbox

Occasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.

Keep reading

Related posts

All posts

Let's build something

Ready to ship your next product with Fepiq?

Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.