Time Functions

I l @ ve RuBoard

The following functions assist in performing calculations based on time- and date-related material. These are useful in performing calculations and transformations of temporal- related data sets.

AGE

Description

The AGE function returns an interval that represents the difference between the current time and the time argument supplied.

Inputs

AGE(timestamp) ”Computes the difference between the timestamp supplied and now() .

AGE(timestamp, timestamp) ”Computes the difference between the two supplied timestamps.

Example

 AGE('03-01-2001 15:56:00', '11-01-2001 14:22:00')  7 mon 30 22:26 ago 

CURRENT_DATE

Description

The CURRENT_DATE function returns the current system date.

Inputs

None.

Example

 SELECT CURRENT_DATE;  2001-06-11 

Notes

Notice that there are no trailing parentheses "()" with this function. This is to maintain SQL compatibility.

CURRENT_TIME

Description

The CURRENT_TIME function returns the current system time.

Inputs

None.

Example

 SELECT CURRENT_TIME;  22:10:31 

Notes

Notice that there are no trailing parentheses "()" with this function. This is to maintain SQL compatibility.

CURRENT_TIMESTAMP

Description

The CURRENT_TIMESTAMP function returns the current system date and time.

Inputs

None.

Example

 SELECT CURRENT_TIMESTAMP;  '2001-06-11 22:10:31-06' 

Notes

Notice that there are no trailing parentheses "()" with this function. This is to maintain SQL compatibility. This function is analogous to the NOW function.

DATE_PART

Description

The DATE_PART function extracts a specified section from the supplied date/time argument.

Inputs

 DATE_PART(  formattext, timestamp  )  DATE_PART(  formattext, interval  ) 

formattext ”One of the valid DATE_PART formatting options; see the following section.

timestamp/interval ”The supplied time-related value.

DATE_PART Formatting Options

The following keywords are recognized as valid date-time elements available for extraction:

Item	Description
millennium	Extracts the year field divided by 1,000.
century	Extracts the year field divided by 100.
decade	Extracts the year field divided by 10.
year	Extracts the year field.
day	The day of the year (1 “366) (timestamp only).
quarter	The quarter of the year (1 “4) (timestamp only).
month	The month of the year (1 “12) (timestamp only).The number of remaining months (interval only).
week	The week number of the year (timestamp only).
dow	The day of the week (0 “6; 0 = Sunday) (timestamp only).
day	The day of the month (1-31) (timestamp only).
hour	The hour field (0-23).
second	The seconds field, including fractional (0 “59.99).
milliseconds	The seconds field, including fractional, multiplied by 1,000.
microseconds	The seconds field, including fractional, multiplied by 1 million.
epoch	The number of seconds since 01-01-1970 00:00 (timestamp). The total number of seconds (interval).

Examples

 DATE_PART('second', TIMESTAMP '06-01-2001 12:23:43')  43  DATE_PART('hour', TIMESTAMP '06-01-2001 12:23:43')  12 

Notes

When using DATE_PART with interval data types, it is import to recognize that DATE_PART will not do implicit calculations. DATE_PART only functions as an extraction tool. For instance, if your interval is 1 month ago , DATE_PART will return 0 (zero) if you tried to extract days .

DATE_TRUNC

Description

The DATE_TRUNC function truncates the supplied timestamp to the specified precision.

Inputs

 DATE_TRUNC(formattext, timestamp) 

formattext ”The precision value to which to truncate the timestamp; see the following valid options for formatting.

timestamp ”The supplied date/time value to truncate.

DATE_TRUNC Formatting Options

The following is a listing of the various levels of precision that DATE_TRUNC can operate on:

Item	Description
millennium	Truncates everything finer than the millennium.
century	Truncates everything finer than the century.
decade	Truncates everything finer than the decade.
year	Truncates everything finer than the year.
month	Truncates everything finer than the month.
day	Truncates everything finer than the day.
hour	Truncates everything finer than the hour.
minute	Truncates everything finer than the minute.
second	Truncates everything finer than the second.
milliseconds	Truncates everything finer than milliseconds.
microseconds	Truncates the microseconds.

Examples

 DATE_TRUNC('hour', TIMESTAMP '2001-11-1 23:11:45')  TIMESTAMP '2001-11-1 23:00:00'  DATE_TRUNC('year' TIMESTAMP '11-1-2001 23:11:45')  TIMESTAMP '2001-01-01 00:00:00' 

EXTRACT

Description

The EXTRACT function extracts the specified value from the supplied timestamp or interval.

Inputs

 EXTRACT(  formattext  FROM  timestamp  )  EXTRACT(  formattext  FROM  interval  ) 

formattext ”A valid date field. Refer to DATE_PART for a listing of valid format codes.

interval/timestamp ”The supplied time value.

Example

 EXTRACT('hour' FROM TIMESTAMP '2001-11-1 23:33:45')  23 

Notes

The EXTRACT function performs like the DATE_PART function. Either syntax can be used interchangeably.

ISFINITE

Description

The ISFINITE function returns a Boolean value that indicates whether the supplied timestamp or interval represents a finite amount of time.

Inputs

 ISFINITE(  timestamp  )  ISFINITE(  interval  ) 

Example

 ISFINITE(TIMESTAMP '2002-05-05 23:13:44')  t 

NOW

Description

The NOW function returns a timestamp that represents the current system time.

Inputs

None.

Example

 SELECT now();  '2001-11-1 15:23:54-06' 

Notes

The NOW function is conceptually the same as CURRENT_TIMESTAMP .

TIMEOFDAY

Description

The TIMEOFDAY function returns a high-precision date and time value.

Inputs

None.

Example

 SELECT TIMEOFDAY();  'Sun Mar 11 22:23:14.853452 2001 CST' 

TIMESTAMP

Description

The TIMESTAMP function works as a conversion routine to convert either date or date and time data types into a timestamp.

Inputs

 TIMESTAMP(date)  TIMESTAMP(date, time) 

Example

 TIMESTAMP('06-01-2001','23:45:11')  '2001-06-01 23:45:11' 

I l @ ve RuBoard