- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- 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 −
It will output the current time. The output will look like the following −
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 −
You can also do these operations on date instead of timestamps
SELECT current_date + interval '3 days'
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
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'
You can read more about the date and time operations in PostgreSQL here − https://www.postgresql.org/docs/9.1/functions-datetime.html
Kickstart Your Career
Get certified by completing the courseGet Started