- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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
- Python Pandas - Get the current date and time from Timestamp object
- Set current date and time to timestamp in MySQL
- How to update a Timestamp and set to current date in MongoDB?
- How to check Leap Year in Java 8 How to get current timestamp in Java 8?
- How to get the seconds and minutes between two Instant timestamps in Java
- How to get timestamp using MySQL?
- How to get time difference between two timestamps in seconds?
- How to get current date and time in Python?
- How to get current time and date in C++?
- How to get the duration between two Instant timestamps in Java
- How to get the Unix timestamp in C#
- MySQL query to get current datetime and only current date
- What is the difference between UNIX TIMESTAMPS and MySQL TIMESTAMPS?