Now, pass the CURRENT_DATE function and the ‘DOW’ as arguments to the DATE_PART() function to fetch the week’s current day as an integer/number: SELECT DATE_PART(CURRENT_DATE, 'DOW') AS current_day As a result, the TO_CHAR() will retrieve the current day of the week: SELECT TO_CHAR(CURRENT_DATE, 'DAY') AS current_day While the DATE_PART() function is used with the “DOW” argument to fetch the day of the week as an integer/numeric value.įor instance, in the following example, we pass CURRENT_DATE and DAY as arguments to the TO_CHAR() function. In Postgres, the TO_CHAR() function is used with the “DAY” argument to get the day of the week as a String. How to Get/Extract the Day of the Week From a Specific Timestamp? The BETWEEN operator queries the employees’ data between the specified dates only. WHERE emp_joining_date BETWEEN '' AND '' For this purpose, use the BETWEEN operator as follows: SELECT emp_name, emp_joining_date Suppose we want to find those employees who joined a company between “ ” and “ ”. How to Query Data Between Two Timestamps Using the BETWEEN Operator? The output shows that the AGE() function retrieves an interval that shows the difference between the current date and the date specified in the “emp_joining_date” column. Let’s learn how to query data between two timestamps using the AGE() function: SELECT emp_name, emp_joining_date,ĪGE(CURRENT_DATE, emp_joining_date) AS Experience Firstly, let’s describe the table’s data: SELECT * FROM emp_data In the following example, we will use the AGE() function to calculate the employee’s total experience in the company. In Postgres, AGE() is a built-in function that is used to calculate the ages/intervals. How to Find Age/Interval Between Two Timestamps in Postgres? This way, you can use these built-in date time functions to query the current date, current time, or both current date and time in PostgreSQL. Use the LOCALTIMESTAMP to query the current timestamp without the time zone. Use the CURRENT_TIMESTAMP to query the current timestamp with the time zone. Use the TIMEOFDAY() function to query the current date, time, and time zone in text format: SELECT TIMEOFDAY() Use the NOW() function to query the current date, time, and timezone: SELECT NOW()

Use the CURRENT_TIME function to query the current time only: SELECT CURRENT_TIME Use the CURRENT_DATE function to query the current date only: SELECT CURRENT_DATE To query the current date or time, the CURRENT_DATE, CURRENT_TIME, NOW(), TIMEOFDAY(), CURRENT_TIMESTAMP, and LOCALTIMESTAMP functions are used in Postgres. These functions allow us to work with the date and time efficiently. PostgreSQL facilitates us with numerous date and time functions, such as CURRENT_DATE, NOW(), EXTRACT(), etc. How to Query Current DateTime in Postgres? How to Convert a Specific Timestamp to a UNIX Timestamp?.How to Get/Extract a Specific Field From the Given Timestamp?.How to Get/Extract the Day of the Week From a Specific Timestamp?.How to Query Data Between Two Timestamps Using the BETWEEN Operator?.How to Find Age/Interval Between Two Timestamps in Postgres?.How to Query Current DateTime in Postgres?.The below-provided topics will be studied in this blog with appropriate examples: This write-up presents a detailed guide on how to query date and time in Postgres. In PostgreSQL, different built-in functions are used along with the SELECT statement to query date and time values.

Postgresql timestamp query