How to Insert Current Date Time in PostgreSQL

PostgreSQL allows you to store different types of data in your databases. Often web developers and database administrators need to insert current date time in PostgreSQL column, either programatically or using queries. In this article, we will learn how to insert current date time in PostgreSQL.

How to Insert Current Date Time in PostgreSQL

Let us say you have the following PostgreSQL table sales (id, product, amount, created_at).

#- create table sales(id int, product varchar(10), amount int, created_at timestamp);

Typically, people use literal strings to enter current date or date time in PostgreSQL as shown below.

#- insert into sales(id, product, amount, created_at) values(1, 'ABC', 180, '2022-11-22');

#- insert into sales(id, product, amount, created_at) values(2, 'ABC', 100, '2022-11-22 10:30:00');

The above methods can be tedious, error prone and inaccurate. Instead you can also use system function current_date to insert current date value, and now() function to insert current date time value in PostgreSQL.

#- insert into sales(id, product, amount, created_at) values(3, 'ABC', 280, current_date);

#- insert into sales(id, product, amount, created_at) values(4, 'ABC', 150, now());

mysql> select * from sales;
+------+---------+--------+---------------------+
| id   | product | amount | created_at          |
+------+---------+--------+---------------------+
|    1 | ABC     |    180 | 2022-11-22 00:00:00 |
|    2 | ABC     |    100 | 2022-11-22 10:30:00 |
|    3 | ABC     |    280 | 2022-11-22 00:00:00 |
|    4 | ABC     |    150 | 2022-11-22 11:14:28 |
+------+---------+--------+---------------------+

You can always change the format of inserted date or time value using type casting operator ::. Here is an example to cast now() function’s output as timestamp.

#- insert into sales(id, product, amount, created_at) values(4, 'ABC', 150, now()::timestamp);

In this article, we have learnt how to insert current date time in PostgreSQL. This is automated and saves you the trouble of manually entering date and time using literal strings. You can use these system functions to capture transaction date time in your database, in case you run a website or app that works on PostgreSQL.

Also read:

How to Insert Current Date Time in MySQL
How to Uninstall MySQL Completely in MySQL
How to View MySQL Query Locking Table
How to Group By Date on Datetime Column in MySQL
How to Select by String Length in MySQL

Sreeram Sreenivasan

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.