Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
2K+ Views
