now() function in SQL

The now() function in SQL is used to retrieve the current timestamp (date + time). It’s particularly useful for calculating durations or querying records relative to the present moment (for example, counting the number of new users in the past 7 days).

Syntax

now() is a scalar (returns a single result) function that doesn’t take any arguments. It returns the current date and time in the format of a timestamp:

SELECT now()

Note how we don’t need a FROM keyword to call a function. This is a useful pattern to test any scalar function.

Using now() to count recent records

Let’s write a query for the example we started with – count users who signed up during the past 7 days:

SELECT
  COUNT(*)
FROM users
WHERE
  created_at > now() - '7 days'::interval

Using now() to calculate time difference

Another useful application of the now() function is to calculate the duration between the current time and a timestamp in the database.

Here’s a query that calculate the age (popular term for time time difference between a timestamp and now) of each signup:

SELECT
  id,
  email,
  now() - created_at AS signup_age
FROM users
LIMIT 5

See also

current_timestamp function in SQL