Functions for getting the current date and time: NOW(), CURRENT_DATE, CURRENT_TIME

PostgreSQL gives you a bunch of built-in functions for working with current time values. These are super handy for stuff like auto-tracking when a row was created, building reports based on the current date, or checking if something happened in a certain period. Let’s check out the three main ones: NOW(), CURRENT_DATE, and CURRENT_TIME.

NOW(): getting the current date and time

The NOW() function returns the current date and time in the TIMESTAMP WITH TIME ZONE format. That means the result includes the exact time plus the server’s time zone.

Example:

SELECT NOW();
-- Result: 2025-05-25 14:30:45.761523+03

Notice that the result includes:

• the date (2025-05-25),
• the time (14:30:45.761523),
• the time zone (+03).

If you don’t care about the time zone, you can explicitly cast the result to TIMESTAMP:

SELECT NOW()::TIMESTAMP;
-- Result: 2025-05-25 14:30:45.761523

CURRENT_DATE: getting the current date

The CURRENT_DATE function returns just the current date, no time attached. The return type is DATE.

Example:

SELECT CURRENT_DATE;
-- Result: 2025-05-25

This is super useful if you don’t care about the time, like when calculating ages or grouping data by day.

CURRENT_TIME: getting the current time

The CURRENT_TIME function gives you the current time in TIME WITH TIME ZONE format. If you don’t need the time zone, you can cast the result to TIME.

Example:

SELECT CURRENT_TIME;
-- Result: 14:30:45.761523+03

SELECT CURRENT_TIME::TIME;
-- Result: 14:30:45.761523

Examples of using these functions

Let’s look at a few practical examples where these functions come in handy.

Auto-filling the creation time of a row

When we add rows to a table, it’s often useful to automatically save the date and time when they were created. In PostgreSQL, you can do this by setting DEFAULT NOW() when creating the table.

Example table creation:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW() -- row creation moment
);

Adding a row:

INSERT INTO orders (customer_name) VALUES ('Otto Lin');

Let’s check out what’s in the table:

SELECT * FROM orders;

Result:

Filtering data by date

Let’s say we have an orders table and want to select all orders created today. We’ll use the CURRENT_DATE function to filter:

SELECT *
FROM orders
WHERE created_at::DATE = CURRENT_DATE;

Here we use created_at::DATE to drop the time part and keep only the date.

Differences between NOW() and CURRENT_TIMESTAMP

At first glance, it looks like NOW() and CURRENT_TIMESTAMP do the same thing. And honestly, they do. It’s just that NOW() comes from one standard and CURRENT_TIMESTAMP from another.

NOW() — PostgreSQL function

The NOW() function is a PostgreSQL built-in function that returns a value of type TIMESTAMP WITH TIME ZONE (timestamptz). It’s the current server time at the start of the SQL query.

Example:

SELECT NOW();

CURRENT_TIMESTAMP — SQL standard

CURRENT_TIMESTAMP is an expression defined by the SQL standard, and it also returns TIMESTAMP WITH TIME ZONE in PostgreSQL. Basically, PostgreSQL implements CURRENT_TIMESTAMP as a call to the same function as NOW().

Example:

SELECT CURRENT_TIMESTAMP;

Comparison in practice

SELECT NOW(), CURRENT_TIMESTAMP;

Result:

Both values are the same because they’re calculated at the same moment — at the start of the query.

Using time functions in filter conditions

Now let’s try writing a query that selects rows created in the last 7 days. You can use NOW() with date arithmetic for this:

SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

Similarly, if you want to select orders for the current month, you can use the DATE_TRUNC() function to chop off the start of the month:

SELECT *
FROM orders
WHERE created_at >= DATE_TRUNC('month', NOW());

DATE_TRUNC() is a cool function, I’ll tell you more about it in a couple of lectures :P

Practical tips

• Use NOW() or CURRENT_TIMESTAMP if you need the exact time with the time zone.
• Go with CURRENT_DATE if you only care about the date (like for age calculations or analyzing events for a specific day).
• CURRENT_TIME is mostly used for tracking time in reports or interfaces where it’s important to show how much time was spent on a task.

In the next lecture, we’ll start extracting parts of dates and times using the EXTRACT() and AGE() functions. With these, you can easily figure out someone’s age or process data by day, month, or year.