Syntax error How to get current timestamp and relative timestamps in PostgreSQL?

How to get current timestamp and relative timestamps in PostgreSQL?

Quite often, you need the current timestamp in PostgreSQL. You do that as follows −

SELECT current_timestamp

It will output the current time. The output will look like the following −

2021-01-30 15:52:14.738867+00

Now, what if you want the relative time instead of the current time? For example, if you want the time corresponding to 5 hours prior to the current time, you can get it using intervals.

SELECT current_timestamp - interval '5 hours'

The output will be different every time. At the time of writing this, the output was −

2021-01-30 10:57:13.28955+00

You can also do these operations on date instead of timestamps

SELECT current_date

Output

2021-01-30
SELECT current_date + interval '3 days'

Output

2021-02-02 00:00:00

Notice how the output here also contains the time component. Because interval comparisons are made on timestamps.

To get only the date part, you can run the following command −

SELECT (current_date + interval '3 days').date

Output

2021-02-02

You can even make compound interval statements. For instance, in India, the timezone difference with UTC is 5 hours 30 minutes. Therefore, to get IST time −

SELECT current_timestamp + interval '5 hours 30 minutes'

Output

2021-01-30 21:31:23.198988+00

You can read more about the date and time operations in PostgreSQL here − https://www.postgresql.org/docs/9.1/functions-datetime.html

Updated on: 2021-02-02T12:54:56+05:30

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements