Date Functions in PostgreSQL
Overview
Date functions in Postgresql allow storing date values in the database enabling data analysis and adding time elements in the queries. It is good to know how data types work in the database we are working in so that reporting of people's age, order information, or any other use case can be accurate and correct.
Date Functions in PostgreSQL
| Function | Return Type | Description |
|---|---|---|
| AGE(timestamp, timestamp) | INTERVAL | Age is calculated between two input time stamps and a symbolic result is returned by it and that result has days, months, and years. |
| AGE(timestamp) | INTERVAL | Age is calculated by it between the timestamp and the present date and a symbolic result is returned by it and that result has days, months, and years. |
| CLOCK_TIMESTAMP | TIMESTAMP | Current time and date are returned by it which is modified during the execution of the statement. |
| CURRENT_DATE | DATE | Current date is returned by it. |
| CURRENT_TIME | TIMESTAMP | Current time is returned by it. |
| CURRENT_TIMESTAMP | TIMESTAMP | The Current date and time are returned by it and it also returns the time zone where the present transaction begins. |
| DATE_PART | DOUBLE PRECISION | Interval or timestamp field is returned by it like day, month, year, etc. |
| DATE_TRUNC | TIMESTAMP | A Timestamp with truncation to the precision specified is returned by it. |
| EXTRACT | DOUBLE PRECISION | Work similar to DATE_PART() function. |
| ISFINITE | BOOLEAN | It checks whether the interval, timestamp or date is finite or infinite. |
| JUSTIFY_DAYS | INTERVAL | The Interval is adjusted so that the 30 days can be defined in the form of month. |
| JUSTIFY_HOURS | INTERVAL | The Interval is adjusted by it so that the 24 hours can be defined in the form of days. |
| JUSTIFY_INTERVAL | INTERVAL | Interval is adjusted by justify_hours, justify_days and by using additional sign adjustments. |
| LOCALTIME | TIME | Time is returned by it at which the present transaction begins. |
| LOCALTIMESTAMP | TIMESTAMP | Date and time are returned by it, where the present transaction begins. |
| NOW | TIMESTAMP | Date and time are returned by it with the time zone at which the present transaction begins. |
| STATEMENT_TIMESTAMP | TIMESTAMP | Current date and time are returned by it at which the present statement runs. |
| TIMEOFDAY | TEXT | The Current date and time are returned by it in the form of a text string. |
| TRANSACTION_TIMESTAMP | TIMESTAMP | Work similar to NOW() function. |
| TO_DATE | DATE | Date is converted to a string. |
| TO_TIMESTAMP | TIMESTAMP | String is converted into a timestamp. |
Examples
Example 1: using AGE(timestamp, timestamp) function
Output: The following result is produced by the execution of the above query. It will display the age between both the timestamps.
| age |
|---|
| 5 years 11 mons 21 days |
Example 2: Using AGE(timestamp) function
Output: The following result is produced by the execution of the above query. It will display the age between timestamp and current time stamp.
| age |
|---|
| 5 years 8 mons 18 days |
Example 3: Using CURRENT_TIME function
Output: The following result is produced by the execution of the above query. It will return the current timestamp.
| current_time |
|---|
| 16:53:34.924016+05:30 |
Example 4: Using CURRENT_DATE function
Output: The following result is produced by the execution of the above query. Current date is returned by this function.
| current_date |
|---|
| 2023-08-27 |
Example 5: Using CURRENT_TIMESTAMP function
Output: The following result is produced by the execution of the above query. It will produce current timestamp as a result.
| current_timestamp |
|---|
| 2023-08-27 16:55:55.493518+05:30 |
Example 6: Using LOCALTIMESTAMP function
Output: The following result is produced by the execution of the above query. It will return the timestamp where the transaction execution started.
| localtimestamp |
|---|
| 2023-08-27 16:56:47.536964 |
Example 7: Using DATE_PART('field', source) function
Output: The following result is produced by the execution of the above query. It will display the day of the date.
| date_part |
|---|
| 12 |
Example 8: Using DATE_PART('field', source) function
Output: The following result is produced by the execution of the above query. Hour of the specified time is displayed by it as an output.
| date_part |
|---|
| 5 |
Example 9: Using DATE_TRUNC('field', source) function
Output: The following result is produced by the execution of the above query. Hour is truncated by it.
| date_trunc |
|---|
| 2013-09-12 10:00:00 |
Example 10: Using DATE_TRUNC('field', source) function
Output: The following result is produced by the execution of the above query. Year of a specified timestamp in truncatenated.
| date_trunc |
|---|
| 2013-01-01 00:00:00 |
Example 11: Using EXTRACT('field', source)
Output: The following result is produced by the execution of the above query. It will extract and display the century of the date as an output.
| extract |
|---|
| 21 |
Example 12: Using EXTRACT('field', source)
Output: The following result is produced by the execution of the above query. It will extract and display the day of the date as an output.
| extract |
|---|
| 12 |
Example 13: Using ISFINITE() function
Output: The following result is produced by the execution of the above query. As specified date in finite, so it will display true.
| isfinite |
|---|
| true |
Example 14: Using ISFINITE() function
Output: The following result is produced by the execution of the above query. As specified timestamp in finite, so it will display true.
| isfinite |
|---|
| true |
Example 15: Using ISFINITE() function
Output: The following result is produced by the execution of the above query. As specified time interval in finite, so it will display true.
| isfinite |
|---|
| true |
Example 16: Using justify_days function
Output: The following result is produced by the execution of the above query. Justify days by converting 30 days into a month.
| justify_days |
|---|
| 1 mon 10 days |
Example 17: Using justify_hours function
Output: The following result is produced by the execution of the above query. Justify interval by converting 24 hours into a day.
| justify_hours |
|---|
| 1 day 06:00:00 |
Example 18: Using justify_interval function
Output: The following result is produced by the execution of the above query. Justify specified interval.
| justify_interval |
|---|
| 1 mon 29 days 21:00:00 |
FAQs
Q. How can we get the current date using date functions in Postgresql?
A. The current_date function is used to get the current date in Postgresql.
Q. How can we extract a month from the date using date functions in Postgresql?
A. We use extract(month from ..) function to extract month from the date.
Q. How can we calculate age of a employee by dob by using date functions in Postgresql?
A. We can use age() for calculating age between current date and birth date.
Conclusion
- Date functions in Postgresql allow storing date values in the database enabling data analysis and adding time elements in the queries.
- AGE(timestamp, timestamp) calculates the age between two input time stamps and a symbolic result is returned by it and that result has months and years.
- ISFINITE() checks whether the interval, timestamp or date is finite or infinite.
- LOCALTIMESTAMP returns the date and time when the present transaction begins.