Chapter 12 – Date Functions

"An inch of time cannot be bought with an inch of gold."

- Chinese Proverb

Current_Date

The Current_Date will return today's date.

TIMEOFDAY()

“Always remember that you are unique just like everyone else.”

– Anonymous

The TIMEOFDAY function returns the weekday, date and the time.

SYSDATE Returns a Timestamp with Microseconds

The SYSDATE function returns the current date and time according to the system clock on the leader node. The functions CURRENT_DATE and TRUNC(SYSDATE) produce the same results.

GETDATE Returns a Timestamp without Microseconds

GETDATE returns a TIMESTAMP. The parentheses are required.

Add or Subtract Days from a date

When you add or subtract from a Date, you are adding/subtracting Days.

Because Dates are stored internally on disk as integers, it makes it easy to add days to the calendar. In the query above, we are adding 60 days to the Order_Date. Also, notice the to_char command which will format the amount.

The ADD_MONTHS Command Returns a Timestamp

The ADD_MONTHS function adds a specified number of months to a date or timestamp value. If date is the last day of the month, or if the resulting month is shorter, the function returns the last day of the month in the result. For other dates, the result contains the same day number as the date expression. A positive or negative integer or any value that implicitly converts to an integer. You can even use a negative number to subtract months from dates. The DATEADD function provides similar functionality.

The ADD_MONTHS Command with Trunc Removes Time

Above, we used the TRUNC command to get rid of the time (00:00:00) on the returning answer set. The ADD_MONTHS function adds a specified number of months to a date or timestamp value. If date is the last day of the month, or if the resulting month is shorter, the function returns the last day of the month in the result. For other dates, the result contains the same day number as the date expression. A positive or negative integer or any value that implicitly converts to an integer. You can even use a negative number to subtract months from dates. The DATEADD function provides similar functionality.

ADD_MONTHS Command to Add 1-Year or 5-Years

There is no Add_Year command, so put in 12 months for 1-year

In this example, we multiplied 12 months times 5 for a total of 5 years!

The Add_Months command adds months to any date. Above, we used a great technique that would give us 1-year. We then showed an even better technique to get 5-years.

Dateadd Function and Add_Months Function are Different

DATEADD

----------------------

2014-05-30 00:00:00

ADD_Months

-------------------------

2014-05-31 00:00:00

The DATEADD and ADD_MONTHS functions handle dates that fall at the ends of months differently.

The EXTRACT Command

The EXTRACT command extracts portions of Date, Time, and Timestamp

“You miss 100 percent of the shots you never take.”

– Wayne Gretzky

This is the Extract command. It returns a date part, such as a day, month, or year, from a timestamp value or expression.

EXTRACT from DATES and TIME

Answer Set

Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.

EXTRACT with DATE and TIME Literals

Answer Set

Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function, and the above is designed to show how to use it with literal values.

EXTRACT of the Month on Aggregate Queries

Answer Set

The above SELECT uses the EXTRACT to only display the month and also to control the number of aggregates displayed in the GROUP BY. Notice the Answer Set headers.

The Datediff command

This function uses a datepart (day, week, month etc.) and two target expressions. This function returns the difference between the two expressions. The expressions must be date or timestamp expressions and they must both contain the specified datepart. If the second date is later than the first date, the result is positive. If the second date is earlier than the first date, the result is negative.

The Datediff Function on Column Data

This function uses a datepart (day, week, month etc.) and two target expressions. This function returns the difference between the two expressions. The expressions must be date or timestamp expressions, and they must both contain the specified datepart. If the second date is later than the first date, the result is positive. If the second date is earlier than the first date, the result is negative.

The Date_Part Function Using a Date

The specific part of the date value (year, month, or day, for example) that the datepart function operates on. The expression must be a date or timestamp expression that contains the specified date_part.

The Date_Part Function Using a Time

“Speak in a moment of anger and you’ll deliver the greatest speech you’ll ever regret.”

– Anonymous

The specific part of the date value (year, month, or day, for example) that the DATE _PART function operates on. The expression must be a date or timestamp expression that contains the specified DATE_PART. Notice that the default column name for the DATE_PART function is PGDATE_PART.

Date_Part Abbreviations

Below are dateparts for date or timestamp functions. The following table identifies the datepart and timepart names and abbreviations that are accepted as arguments to the following functions:

Above are the functions for datepart or timepart, their parts, and the acceptable abbreviations.

The to_char command

The to_char command will take a value and convert it to a character string.

Conversion Functions

Function Name	Conversion Operation
to_number()	Character to numeric
to_date()	Character or timestamp to date
to_timestamp()	Character to timestamp
to_char()	Numeric, date or timestamp to character

The following shows the syntax for using these functions:

,to_number(<character-data>,'<template>')

,to_date(<character-data>,'<template>')

,to_timestamp(<character-data>,'<template>')

,to_char(<numeric-data>)

,to_date(<character-data>,'<template>') ;

The NPS database provides some functions that assist in the conversion of data from one type to another.

Conversion Function Templates

HH, HH12	Hour of day (01:12).
HH24	Hour of day (00:23).
MI	Minute (00:59).
SS	Second (00:59).
SSSS	Seconds past midnight (0:86399).
AM, am, A.M., a.m. or PM, pm, P.M., p.m.	Meridian indicator (uppercase and lowercase).
Y,YYY	Year (4 and more digits) with a comma.
YYYY	Year (4 and more digits).
YYY	Last 3 digits of the year.
YY	Last 2 digits of the year.
Y	Last digit of the year.
MONTH, Month, month	Full month name (blank-padded to 9 chars).
MON, Mon, mon	Abbreviated uppercase month name (3 chars).
MM	Month number (01:12).
DAY, Day, day	Full day name (blank-padded to 9 chars).
DY, Dy, dy	Abbreviated uppercase day name (3 chars).
DDD	Day of the year (001:366).
DD	Day of the month (01:31).
D	Day of the week (1:7; SUN=1).
BC, bc, B.C., b.c or AD, ad, A.D., a.d.	Era indicator (uppercase and lowercase).

Conversion Function Templates Continued

W	Week of the month (1:5) where first week start on the first day of the month.
WW	Week number of the year (1:53) where the first
week	starts on the first day of the year.
IW	ISO week number of the year (The first Thursday of the new year is in week 1.)
CC	Century (2 digits).
J	Julian Day (days since January 1, 4712 BC).
Q	Quarter
RM	Month in Roman Numerals (I-XII; I=January) — uppercase.
rm	Month in Roman Numerals (i-xii; i=January) — lowercase.
FM prefix	Fill mode (suppresses padding blanks and zeroes).
TH, th suffix	Add uppercase ordinal number suffix.
FX prefix	Fixed format global option.
9	Value with the specified number of digits.
0	Value with leading zeros.
. (period)	Decimal point.
, (comma)	Group (thousand) separator.
PR	Negative value in angle brackets.
S	Negative value with minus sign (uses locale).
L	Currency symbol (uses locale).
D	Decimal point (uses locale).
G	Group separator (uses locale).
MI	Minus sign in the specified position (if number < 0).
RN	Roman numeral (input between 1 and 3999).
V	Shift n digits (see notes).

Formatting a Date

The to_char command will take a value and convert it to a character string. This includes formatting a date.

A Summary of Math Operations on Dates

   DATE – DATE = Interval (days between dates)

   DATE + or - Integer = Date

Let's find the number of days Tera-Tom has been alive since his last birthday.

A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. The query above uses the dates the traditional way to deliver the Interval.

Using a Math Operation to find your Age in Years

   DATE – DATE = Interval (days between dates)

   DATE + or - Integer = Date

Let's find the number of days Tera-Tom has been alive since his last birthday.

Let's find the number of years Tera-Tom has been alive since his last birthday.

A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. Both queries above perform the same function, but the top query uses the date functions to find "Days" and the query on the bottom finds "Years".

Date Related Functions

Answer Set

A Side Title example with Reserved Words as an Alias

The next SELECT operation uses entirely ANSI compliant code to show the month and day of the payment due date in 2 months and 4 days. Notice it uses double quotes to allow reserved words as alias names.

Implied Extract of Day, Month and Year

--The following SELECT uses math to extract the three portions of Tom's literal birthday

It was mentioned earlier that Matrix stores a date as an integer and therefore allows math operations to be performed on a date. Although the EXTRACT works great and it is ANSI compliant, it is a function. Therefore, it must be executed and the parameters passed to it to identify the desired portion as data. Then, it must pass back the answer. As a result, there is additional overhead processing required to use it.

DATE_PART Function

The following SELECT will show DATE_PART with a Date, Time, and Timestamp.

The DATE_PART function works exactly like EXTRACT. Although the name contains DATE, it also works with time and time stamp data. Notice the column headers!

DATE_PART Function using an ALIAS

The following SELECT will show DATE_PART with a Date,
Time, and Timestamp and add an ALIAS for each DATE_PART.

The DATE_PART function works exactly like EXTRACT. Although the name contains DATE, it also works with time and time stamp data. Now notice the column headers!

DATE_TRUNC Function

The DATE_TRUNC function has an interesting capability in that it truncates the portion of a date back to the first. Notice that the year portion becomes January 1, the month portion becomes August 1, and the day portion does not change. However, the entire time portion is set back to 12:00:00. When DATE data is used, the time portion is set to 12:00:00 just like in the above example.

DATE_TRUNC Function using TIME

Compatibility:Matrix Extension.Syntax of DATE_TRUNC:

DATE_TRUNC('<text',<date>)

Where <text> can be: 'YEAR', 'MONTH', 'DAY' for a date or a time stamp and ('HOUR', 'MINUTE', 'SECOND' for time and time stamp. Although DAY and SECOND are allowed, they have no impact on the output data, see below.

Notice that the hour portion becomes 9:00:00, the minute portion becomes 9:05:00, and the second and date portions do not change. If TIME was used, there would be no DATE portion as in a TIME STAMP.

MONTHS_BETWEEN Function

Compatibility:Matrix Extension

Syntax:MONTHS_BETWEEN (<start_date>,<end_date>)

The following example uses the MONTHS_BETWEEN with some fixed dates

The MONTHS_BETWEEN function is handy for doing date subtraction. Unlike normal date subtraction, it returns a fractional portion based on the days in a month.

MONTHS_BETWEEN Function in Action

SELECT ADD_MONTHS(Order_Date, 2) AS"Due Date"

,Order_Date

,to_char(Order_total,'$99,999.99')

,MONTHS_BETWEEN(Add_Months(order_date,2),order_date)

FROMOrder_Table

ORDER BY 2 ;

The above example uses the order table to demonstrate BETWEEN_MONTHS.

ANSI TIME

Matrix has the ANSI time display and TIME data type.
CURRENT_TIME is the ANSI name of the time function.

TIMESubtract

17:27:5617:27:01

As well as creating a TIME data type, intelligence has been added to the clock software. It can increment or decrement TIME with the result increasing to the next minute or decreasing from the previous minute based on the addition or subtraction of seconds.

ANSI TIMESTAMP

Notice that there is a space between the DATE and TIME portions of a TIMESTAMP. This is a required element to delimit or separate the day from the hour.

Matrix TIMESTAMP Function

What a wonderful feature. Matrix allows you to convert a date or a combination of a date and a time into a Timestamp. The example above shows an example of converting a date, a date and time, and a literal date and time. This should be all you need.

Matrix TO_TIMESTAMP Function

Matrix allows you to convert character strings into a Timestamp. Notice that both answers are exactly the same. The second parameter is NOT how the data should be output or formatted, but instead it reflects how the string should be interpreted.

Matrix NOW() Function

Compatibility: Matrix Extension

The timestamp can also be obtained using the NOW() function:

SELECT NOW() ;

Matrix allows you to see the date and time with the NOW() function. The next time someone asks for the time tell them NOW.

Matrix TIMEOFDAY Function

Compatibility:Matrix Extension

To get a bit more extended version of a time stamp use TIMEOFDAY:

Answer Set

Matrix allows you an extended version of a time stamp that is robust and verbose.

Matrix AGE Function

Compatibility: Matrix Extension

The AGE function returns the interval (discussed later in this chapter) between two time stamps. If you use a single time stamp, the age function returns the interval between the current time and the time stamp provided. The interval returned by the age function can include year and month data as well as day and time data.

Syntax of AGE:

AGE(<start-date>,<end-date>)

Answer Set

To subtract one time stamp from another, use the AGE function.

Time Zones

Matrix has the ability to adjust the time and timestamp values to reflect the hours difference between the user’s time zone, the system time zone, and the United Kingdom location that was historically called Greenwich Mean Time (GMT). Since the Greenwich observatory has been "decommissioned," the new reference to this same time zone is called Universal Time Coordinate (UTC).

Here, the time zones used are represented from the perspective of the system at EST. In the above, it appears to be backward. This is because the time zone is set using the number of hours that the system is from the user.

Setting Time Zones

A Matrix session can modify the time zone during normal operations without requiring a logoff and logon. At this time, the NPS only recognizes time zone processing stored in a table with data type of TIME WITH TIME ZONE. Hopefully, it will soon also be added to TIMESTAMP when stored in a table.

Using Time Zones

The way time zones are implemented in Matrix is that the session time zone setting adjusts the value returned by the TIME and TIMESTAMP when referenced in an SQL statement. To make some of the changes more apparent, the following statements "assume" that they are all run at the same time.

Examples above set the time zone and then query Current_Timestamp simultaneously.

Intervals for Date, Time and Timestamp

“It’s not the size of the dog in the fight, but the size of the fight in the dog.”

– Archie Griffin

Matrix has added INTERVAL processing, however, it is not ANSI compliant. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.

Using Intervals

“The afternoon knows what the morning never suspected.”

- Swedish Proverb

To use the ANSI syntax for intervals, the SQL statement must be very specific as to what the data values mean and the format in which they are coded. ANSI standards tend to be lengthier to write and more restrictive as to what is and what is not allowed regarding the values and their use.

Troubleshooting the Basics of a Simple Interval

 Our_Date  Leap_Year 

01/29/201202/29/2012

Error – Invalid Date

The first example works because we added 1 month to the date '2012-01-29' and we got '2012-02-29'. Because this was leap year, there actually is a date of February 29, 2012. The next example is the real point. We have a date of '2011-01-29' and we add 1-month to that, but there is no February 29^th in 2011, so the query fails.

Interval Arithmetic Results

To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations. The above chart is your Interval guide.

DATE and TIME arithmetic results using intervals:

“Once the game is over, the king and the pawn go back in the same box.”

- Italian Proverb

To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations. The above chart is your Interval guide.

A Date Interval Example

ERROR – Interval Field Overflow

The Error occurred because the default for all intervals is 2 digits.

The default for all intervals is 2 digits. We received an overflow error because the Actual_Days is 4017. The second example works because we demanded the output to be 4 digits (the maximum for intervals).

A Time Interval Example

ERROR – Interval Field Overflow

The default for all intervals is 2 digits, but notice in the top example, we put in 3 digits for Minute, 4 digits for Second, and 4,4 digits for the Acutal_Seconds4. If we had not, we would have received an overflow error as in the bottom example.

A DATE Interval Example

The above Interval example uses a –'2' to go back in time.

A Complex Time Interval Example using CAST

Below is the syntax for using the CAST with a date:

SELECT CAST (<interval> AS INTERVAL <interval> )
FROM <table-name> ;

The following converts an INTERVAL of 6 years
and 2 months to an INTERVAL number of months:

  6-02  

74

The CAST function (Convert And Store) is the ANSI method for converting data from one type to another. It can also be used to convert one INTERVAL to another INTERVAL representation. Although the CAST is normally used in the SELECT list, it works in the WHERE clause for comparison reasons.

A Complex Time Interval Example using CAST

ERROR

 Years & Month

108-04

The top query failed because the INTERVAL result defaults to 2-digits and we have a 3-digit answer for the year portion (108). The bottom query fixes that specifying 3-digits. The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible.

The OVERLAPS Command

Compatibility: Matrix Extension

The syntax of the OVERLAPS is:

SELECT <literal>

WHERE (<start-date-time>, <end-date-time>)    OVERLAPS

(<start-date-time>, <end-date-time>) ;

When working with dates and times, sometimes it is necessary to determine whether two different ranges have common points in time. Matrix provides a Boolean function to make this test for you. It is called OVERLAPS; it evaluates true if multiple points are in common, otherwise it returns a false. The literal is returned because both date ranges have from October 15 through November 30 in common.

An OVERLAPS Example that Returns No Rows

The above SELECT example tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal. The literal was not selected because the ranges do not overlap. So, the common single date of November 30 does not constitute an overlap. When dates are used, 2 days must be involved, and when time is used, 2 seconds must be contained in both ranges.

The OVERLAPS Command using TIME

The above SELECT example tests two literal times and uses the OVERLAPS to determine whether or not to display the character literal. This is a tricky example, and it is shown to prove a point. At first glance, it appears as if this answer is incorrect because 02:01:00 looks like it starts 1 second after the first range ends. However, the system works on a 24-hour clock when a date and time (timestamp) is not used together. Therefore, the system considers the earlier time of 2AM time as the start and the later time of 8 AM as the end of the range. Therefore, not only do they overlap, the second range is entirely contained in the first range.

The OVERLAPS Command using a NULL Value

The above SELECT example tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:

When using the OVERLAPS function, there are a couple of situations to keep in mind:

1. A single point in time, i.e. the same date, does not constitute an overlap. There must be at least one second of time in common for TIME or one day when using DATE.

2. Using a NULL as one of the parameters, the other DATE or TIME constitutes a single point in time versus a range.