PostgreSQL - SPLessons

PostgreSQL Date Functions

Home > Lesson > Chapter 14
SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Date Functions

PostgreSQL Date Functions

shape Description

PostgreSQL Date Functions datatypes can be broadly characterized into two types, they are:

Interval Data Type

shape Description

The values of interval datatypes are called as intervals. And, these intervals are classified into 2 types, such as:

INTERVAL DAY TO SECOND

It stores the timeframe including seconds,minutes, hours and days.

INTERVAL YEAR TO MONTH

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.

PostgreSQL Date time datatype

shape Description

PostgreSQL Date time data types are further classified into 4 types, such as:

DATE

Date and time information will be stored in 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;

TIMESTAMP

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.
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 WITH TIME ZONE

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.
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

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

Summary

shape 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.