Postgres Now Function
Introduction
When you’re using PostgreSQL to store your data, there are times when you’ll need to access the current date and time. PostgreSQL makes it easy to get this value with the NOW() function. In this article, we’ll show examples of how to use the PostgreSQL NOW function in different ways.
Prerequisites
For this tutorial, there’s only one important prerequisite that needs to be in place before proceeding: A PostgreSQL database cluster must be installed on your machine. To check whether it’s installed, use the command service PostgreSQL status in the psql command-line interface; once you get a response, type CTRL + C to exit.
Use psql to connect to a database
We’ll need to be in the psql command-line interface to try out the NOW() function. To access a PostgreSQL database with this interface, we’ll use the following command:
1 | sudo su Postgres |
We’ll then use the command psql to manage the database and perform our SQL queries.
Postgres NOW() function
Once we’ve successfully entered the psql interface, we can use a SQL SELECT statement to return all date and time values that result from calling the NOW() function:
1 | SELECT NOW(); |
It will return a string representing the current time down to the exact millisecond. This string will look like the following:
1 | 2019-12-13 08:43:41.524649+00 |
The last two digits at the end (+00) represent the UTC (Coordinated Universal Time) time difference in hours—this value indicates your PostgreSQL time zone.
Postgres timestamp without time zone
We can append ::TIMESTAMP to the end of our SQL statement to omit the time zone from the response:
1 | SELECT NOW()::TIMESTAMP |
Set the PostgreSQL time zone
To change the Postgres time zone, use the SQL SET command as shown below:
1 | SET TIMEZONE='Asia/Manila'; |
If the time zone string provided for SET TIMEZONE is invalid, Postgres will default to the UTC (00) time zone.
PostgreSQL select current year
Let’s see how we can use the NOW() function to get the current year in PostgreSQL:
1 | SELECT EXTRACT( YEAR FROM NOW() ); |
This command should return a date_part response that looks something like the following:
1 2 3 4 | date_part ----------- 2019 (1 ROW) |
PostgreSQL Unix timestamp
In the following example, the EXTRACT() function makes use of the NOW() function to return an integer representing the current Unix epoch time:
1 | SELECT EXTRACT(EPOCH FROM NOW()); |
It should return something like the following:
1 2 3 4 5 | dbtest=# SELECT EXTRACT(EPOCH FROM NOW()); date_part ------------------- 1576245400.716711 (1 ROW) |
Create a PostgreSQL table with a timestamp column
Next, let’s create a table that will get the current date, time and time zone for a record in the table.
Here’s the SQL statement we’ll use to create our PostgreSQL table:
1 | CREATE TABLE some_tbl(some_str TEXT, date_today TIMESTAMPTZ DEFAULT NOW()); |
Now we’ll insert a value inside the table:
1 2 | INSERT INTO some_tbl(some_str) VALUES('Hello World!'); |
Once we have at least one record in our table, we can use the SELECT statement to query it:
1 2 3 4 5 | dbtest=# SELECT * FROM some_tbl; some_str | date_today --------------+------------------------------- Hello World! | 2019-11-24 09:34:17.864167+08 (1 ROW) |
Here’s another example that retrieves the date and time using the NOW function:
1 2 3 4 5 | SELECT NOW() AS DATE_CREATED_TODAY; date_created_today ------------------------------- 2019-11-24 09:40:21.438776+08 (1 ROW) |
Set the ‘NOW’ interval
Our next example will demonstrate how to set an interval when using the NOW(); function within a transaction.
First we’ll BEGIN our transaction:
1 2 | dbtest=# BEGIN; BEGIN |
This notifies the SQL command console to begin the transaction.
1 | SELECT NOW(); |
To set the interval, we’ll use pg_sleep:
1 | SELECT pg_sleep; |
We’ll then select the time again for comparison purposes after the five-second sleep:
1 | SELECT NOW(); |
Finally, we’ll end the transaction by using COMMIT:
1 2 | dbtest=#COMMIT; COMMIT |
The result of setting an interval in a PostgreSQL NOW function using a transaction will look like the following:
1 2 3 4 | date_and_time | pg_sleep | date_and_time -------------------------------------+----------+------------------------------------- Sun Nov 24 11:13:13.617377 2019 PST | | Sun Nov 24 11:13:18.622591 2019 PST (1 ROW) |
NOTE: When you try to use the
NOW()function inpsql, you’ll get an interval of five seconds before it displays the result of the current time and date.
Another way to get the interval is to select the date and time operator using the NOW() function.
For example:
1 2 3 4 5 | dbtest=# SELECT (NOW() + INTERVAL '10 minutes') AS interval_time; interval_time ------------------------------- 2019-11-25 10:57:58.949612+08 (1 ROW) |
This is used to get the time that’s ten minutes ahead of the current time on your machine.
You can also use the - operator to get the time and date from a certain interval in the past:
Here’s an example:
1 | SELECT NOW() - INTERVAL '10 minutes' AS ago; |
The command shown above should return something like:
1 2 3 | ------------------------------- 2019-11-25 10:44:00.984711+08 (1 ROW) |
Conclusion
Being able to retrieve the current date and time is an essential skill when you’re working with data in PostgreSQL. In this article, we took a close look at the Postgres NOW function, and we learned how to use it in different ways to get the current time as well as times from a past or future interval. With our examples to use as a guide, you’ll be prepared to use the NOW() function in your own database commands.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started