Description
PostgreSQL Date Functions datatypes can be broadly characterized into two types, they are:
- Interval data types
- Date time data types
Description
The values of interval datatypes are called as
It stores the timeframe including
intervals. And, these intervals are classified into 2 types, such as:
- INTERVAL DAY TO SECOND
- INTERVAL YEAR TO Month
seconds,minutes, hours and days.
It stores the timeframe including the MONTH and YEAR date time fields.
The following table illustrates the function name and purpose in interval datatypes.
| S.NO | Function Name | Description |
|---|---|---|
| 1 | TO_DSINTERVAL(X); | Changes the string x to INTERVAL DAY TO SECOND. |
| 2 | TO_YMINTERVAL(X); | Changes the string x to INTERVAL YEAR TO MONTH. |
| 3 | NUMTODSINTERVAL(x,interval_unit); | Changes the number x to INTERVAL DAY TO SECOND. |
| 4 | NUMTOYMINTERVAL(x,interval_unit); | Changes the number x to INTERVAL YEAR TO MONTH. |
Description
PostgreSQL Date time data types are further classified into 4 types, such as:
Date and time information will be stored in
TIMESTAMP is used to store the exact time values. It will store the year, month and day of the datatype along with hours, minutes and seconds. The TIMESTAMP is an expansion of DATE Datatype.
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that comprises of a period zone region or time zone offset character & values. The offset time zone is the difference between the inclusive time zone and the local time zone.
- DATE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
number and character datatypes. The information is represented in seconds, minutes, hours, date, month, years and decades.
| S.NO | Function Name | Purpose |
|---|---|---|
| 1 | NEW_TIME; | Returns the time/day value from a period zone specified by the client. |
| 2 | NEXT_DAY(x,y); | Returns the date time of the following day after x. |
| 3 | LAST_DAY(X); | Returns the most recent day of the month. |
| 4 | ADD_MONTHS(x,y); | Adds y months to x. |
| 5 | MONTHS_BETWEEN(x,y); | Returns the number of months between x and y. |
| 6 | SYSDATE(); | Returns the present date time. |
| 7 | TRUNC(x[,unit]); | Truncates x. |
| 8 | ROUND(x[,unit]); | Rounds x; |
| S.NO | Function Name | Purpose |
|---|---|---|
| 1 | LOCALTIMESTAMP(); | Returns a TIMESTAMP consisting appropriate time in the session time zone. |
| 2 | SYSYIMESTAMP(); | Returns TIMESTAMP WITH TIME ZONE consisting the current database time along with the database time zone. |
| 3 | CURRENT_TIMESTAMP(); | Returns TIMESTAMP WITH TIME ZONE consisting the current session time along with the session time zone. |
| 4 | TO_TIMESTAMP_TZ(x,[format]); | Changes the string x to TIMESTAMP WITH TIMEZONE. |
| 5 | TO_TIMESTAMP(x,[format]); | Change the string x to TIMESTAMP. |
| 6 | SYS_EXTRACT_UTC(X); | Changes the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP consisting the date and time in UTC. |
| 7 | FROM_TZ(X,time_zone); | Changes the TIMESTAMP x and time zone indicated by time_zone to a TIMESTAMP WITH TIMEZONE. |
TIMESTAMP[(fractional_seconds_precisions)]WITH TIME ZONE
Fractional_seconds_precisions => Determines the number of digits in fractional parts.
Eg:TIMESTAMP '1999-01-31 09:25:56:66 +02:00'
TIMESTAMP WITH LOCAL TIME ZONE is considered as another variant of TIMESTAMP that contains a period zone balance in its qualities.
TIMESTAMP[(fractional_seconds_precisions)] WITH LOCAL TIME ZONE
Fractional_seconds_precisions => Determines the number of digits in fractional parts.
TIMESTAMP => No literal TIMESTAMP exists
Key Points
- PostgreSQL Date Functions - Stores character and number datatypes.
- Interval Data types - Also called as intervals.
- Interval Day to second - Stores timeframe in seconds, minutes and hours.
- Interval year to month - Stores timeframe using the month and year.
- Timestamp - Used for storing the exact time values.
- Timestamp with time zone - Contains the variant of TIMESTAMP.
- Timestamp with local time zone - Considers another variant of TIMESTAMP.