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