Chapter 9 – 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.
Current_Date, Current_Time and Current_Timestamp
Above are the keywords you can utilize to get the date, time or timestamp. These are reserved words that the system will deliver to you when requested.
Timestamp Differences
SELECT Current_Timestamp(0) AS Col1
,Current_Timestamp(6) AS Col2
A timestamp has the date separated by a space and the time. In our second example we have asked for 6 microseconds.
Getdate
This example uses the Getdate() function to return the timestamp.
SELECT Getdate() as "The Date";
The Date
-------------
03/30/2015 8:46:04.567
“Not all who wander are lost.”
– J. R. R. Tolkien
The Getdate command will return today's date and time just like the Current_Timestamp command. This is not ANSI.
Date and Time Keywords
SELECT
GETDATE()AS 'GETDATE'
, CURRENT_TIMESTAMPAS 'CURRENT_TIMESTAMP'
, GETUTCDATE()AS 'GETUTCDATE'
The above example shows another way to get the date and time. The GETDATE and CURRENT_TIMESTAMP are equivalent, but CURRENT_TIMESTAMP is ANSI compliant.
Using CAST in Literal Values
SELECT CAST('20150216' AS DATE) as "Date YMD";
Date YMD
2015-02-16
This is an example of using the CAST function with a date literal.
Add or Subtract Days from a date
SELECT Order_Date
,Order_Date + 60 as "Due Date"
,Order_Total
,Order_Date + 50as Disc_Date
,Order_Total *.98 as Disc_Amt
FROM Order_Table
ORDER BY 1 ;
When you addor 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.
Formatting Dates
Vertica gives you many options for formatting dates. The next page will show an example.
Formatting Date Example
SELECT Order_Date
,TO_CHAR(Order_Date , 'YY-MM-DD') AS YMD
,TO_CHAR(Order_Date , 'MON, DD, YYYY') AS Month
,TO_CHAR(Order_Date , 'D, Mon DD, YY') AS DayofWeek
,Current_Time as Time
,TO_CHAR(Current_Time , 'HH24:MI:SS:MS') AS Micro
FROMOrder_Table
WHERE EXTRACT(Year from Order_Date) = 1998 ;
Above you can see an example of formatted dates using the TO_CHAR command.
A Summary of Math Operations on Dates
|
DATE - DATE = Interval (days between dates) |
|
DATE + or - Integer = Date |
SELECT
Order_Number
,Order_Total
,Order_Date
,Order_Date - 365 as Last_Year
, Current_Date - Order_Date as Days_Between
FROM Order_Table
A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date.
The ADD_MONTHS Command
SELECT Order_Date,
,Add_Months (Order_Date,2) as "Due Date2"
,Order_Total
FROM Order_Table ORDER BY 1 ;
This is the Add_Months Command. What you can do with it is add a month or many months to your date columns. Can you convert this to one year? There is no ADD_YEAR command!
Using the ADD_MONTHS Command to Add 1 Year
SELECT Order_Date
,Add_Months (Order_Date,12) as "Due Date12"
,Order_Total
FROM Order_Table ORDER BY 1 ;
The Add_Months command adds months to any date. Above we used a great technique that would give us 1 year. Can you give me 5 years?
Using the ADD_MONTHS Command to Add 1 Year
SELECT Order_Date
,Add_Months (Order_Date,12) as "Due Date12"
,Order_Total
FROM Order_Table ORDER BY 1 ;
The Add_Months command adds months to any date. Above we used a great technique that would give us 1 year. Can you give me 5 years?
Using the ADD_MONTHS Command to Add 5 Years
Above you see a great technique for adding multiple years to a date.
The EXTRACT Command
This is the Extract command. It returns a date part, such as a day, month or year, from a timestamp value or expression. It can be used in the SELECT list, the WHERE Clause, or the ORDER BY Clause!
YEAR, MONTH, and DAY Functions
SELECT Order_Date
,Year(Order_Date) as "Yr"
,Month(Order_Date) as "Mo"
,Day(Order_Date) as "Day"
FROM Order_Table
ORDER BY 1 ;
The YEAR, MONTH and DAY functions are abbreviations for the DATEPART function.
A Better Technique for YEAR, MONTH, and DAY Functions
Above are the tale of two queries. The top query applies manipulation on the filtered column. Yet, in most cases Vertica can’t use an index efficiently when using this technique. The bottom query uses a range filter instead. Brilliant!
Another Version of the EXTRACT Command
The EXTRACT command extracts portions of Date, Time, and Timestamp
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due Date"
,Order_Total
FROM Order_Table
WHERE EXTRACT(Month from Order_Date) = 9 ;
Below is another version of the Extract Command
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due Date"
,Order_Total
FROM Order_Table
WHERE Month (Order_Date) = 9 ;
Both examples above are equivalent, but beware! The EXTRACT command is a better form because it also works on Day, Year, Hour, Minute and Second. The example on the bottom won't work with all of them.
EXTRACT from DATES and TIME
SELECT Current_Date as Date
,EXTRACT(Year from Current_Date) as Yr
,EXTRACT(Month from Current_Date) as Mo
,EXTRACT(Day from Current_Date) as Da
,Current_Time as Time
,EXTRACT(Hour from Current_Time) as Hr
,EXTRACT(Minute from Current_Time) as Mn
,EXTRACT(Second from Current_Time) as Sc
,EXTRACT(TIMEZONE_HOUR from Current_Time) as Th
,EXTRACT(TimeZONE_MINUTE from Current_Time) as Tm ;
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.
Why EXTRACT is a Better Form
Most extracts are on the month or year. That can be done using either technique above.
EXTRACT with DATE and TIME Literals
SELECT
EXTRACT(Year FROM Date '2000-10-01') AS "YR"
,EXTRACT(Month FROM Date '2000-10-01') AS "Mth"
,EXTRACT(DAY FROM Date '2000-10-01') AS 'Day'
,EXTRACT(HOUR FROM TIME '10:01:30') AS 'Hr'
,EXTRACT(MINUTE FROM TIME '10:01:30') AS 'Min'
,EXTRACT(SECOND FROM TIME '10:01:30') AS 'Sec'
,EXTRACT(MONTH FROM Current_Timestamp) AS ts_Mth
,EXTRACT(SECOND FROM Current_Timestamp) AS ts_Part
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function. The query above is designed to show how to use it with literal values.
EXTRACT of the Month on Aggregate Queries
SELECT
EXTRACT(Month FROM Order_Date)
,COUNT(*) as "Rows"
,AVG(Order_Total) as "AVG"
FROM Order_Table
GROUP BY 1
ORDER BY 1
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.
AGE_IN_MONTHS
Above you see a great technique for seeing the age in months between two dates or timestamps.
AGE_IN_YEARS
Above you see a great technique for seeing the age in years between two dates or timestamps.
DATE_TRUNC
SELECT
Current_Date as "Today"
,DATE_TRUNC('Century', Current_Date) as "Century"
,DATE_TRUNC('Day', Current_Date) as "Day"
SELECT
Current_Time as "Time"
,DATE_TRUNC('Minute', Current_Time) as "Minute"
,DATE_TRUNC('Hour', Current_Time) as "Hour"
,DATE_TRUNC('Microseconds', Current_Time) as "Micro"
The Date_Trunc function truncates date and time values as specified.
DATEDIFF
SELECT
Current_Date as "Date Today"
,Order_Date
,DATEDIFF(Year, Order_Date, Current_Date) as "Years"
,DATEDIFF(Quarter, Order_Date, Current_Date) as "Quarters"
,DATEDIFF(Month, Order_Date, Current_Date) as "Months"
,DATEDIFF(Day, Order_Date, Current_Date) as "Days"
,DATEDIFF(Week, Order_Date, Current_Date) as "Weeks"
,DATEDIFF(Hour, Order_Date, Current_Date) as "Hours"
FROM Order_Table
WHERE EXTRACT(Year from Order_Date) = 1998 ;
The DATEDIFF function Returns the difference between two date or time values based on the specified start and ending arguments. The DATEDIFF function includes all of the above plus minute, second, millisecond and microsecond.
DAYOFWEEK
SELECT
Current_Date as "Date Today"
,DAYOFWEEK(Current_Date)
,CASE DAYOFWEEK(Current_Date)
WHEN 1 Then 'Sunday'
WHEN 2 Then 'Monday'
WHEN 3 Then 'Tuesday'
WHEN 4 Then 'Wednesday'
WHEN 5 Then 'Thursday'
WHEN 6 Then 'Friday'
WHEN 7 Then 'Saturday'
END as WhatDayIsIt
The DAYOFWEEK function returns a 1 if the day of the week is Sunday, 2 if Monday and so on.
Intervals for Date, Time and Timestamp
Interval Chart |
|
Simple |
More involved |
YEAR |
DAY TO HOUR |
MONTH |
DAY TO MINUTE |
DAY |
DAY TO SECOND |
HOUR |
HOUR TO MINUTE |
MINUTE |
HOUR TO SECOND |
SECOND |
MINUTE TO SECOND |
“It’s not the size of the dog in the fight, but the size of the fight in the dog.”
– Archie Griffin
Vertica has added INTERVAL processing, however, it is not ANSI compliant. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.
Interval Data Types and the Bytes to Store Them
Above are the interval data types and the bytes to store them.
Using Intervals
SELECT Current_Date as Our_Date
,Current_Date + Interval '1' Day as Plus_1_Day
,Current_Date + Interval '3' Month as Plus_3_Months
,Current_Date + Interval '5' Year as Plus_5_Years
SELECT
Current_Date as Our_Date
,CAST(Current_Date + Interval '1' Day as Date) as Plus_1_Day
,CAST(Current_Date + Interval '3' Month as Date) as Plus_3_Months
,CAST(Current_Date + Interval '5' Year as Date) as Plus_5_Years
Above we are using simple intervals. Notice in the first example the time added to the interval. Notice the second example has used the CAST (Convert and Store) technique. Either way, the intervals have been added.
How a Simple Interval Handles Leap Year
SELECT Date '2012-01-29' as Our_Date
,Date '2012-01-29' + INTERVAL '1' Month as Leap_Year
Our_Date |
Leap_Year |
01/29/2012 |
02/29/2012 12:00:00.000000 |
SELECT Date '2011-01-29' as Our_Date
,Date '2011-01-29' + INTERVAL '1' Month as Leap_Year
Our_Date |
Leap_Year |
01/29/2011 |
02/28/2011 12:00:00.000000 |
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 29th in 2011, so the query places the day at 02/28/2011.
Interval Arithmetic Results
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 Time Interval Example
Time intervals work as you can see from the example above.
A DATE Interval Example Going Back in Time
“I know that you believe that you understand what you think I said, but
I am not sure you realize that what you heard is not what I meant.”
-Sign on Pentagon office wall
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:
SELECT
CAST( (INTERVAL '6-02' YEAR TO MONTH) AS INTERVAL MONTH ) Mths
Mths
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
The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible.
The OVERLAPS Command
Compatibility: Vertica Extension
The syntax of the OVERLAPS is:
SELECT <literal>
WHERE (<start-date-time>, <end-date-time>)
OVERLAPS
(<start-date-time>, <end-date-time>) ;
SELECT 'The Dates Overlap' as Dater
WHERE (DATE '2001-01-01', DATE '2001-11-30')
OVERLAPS
(DATE '2001-10-15', DATE '2001-12-31');
When working with dates and times, sometimes it is necessary to determine whether two different ranges have common points in time. Vertica 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. 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.