Date Operations
Cloud9QL will automatically attempt to parse various date formats.
Use str_to_date(<date>,<format>) for unsupported formats.
Try Date operators below using the interactive widget, with the default data or your own:
DATE
Truncates a date to midnight. When used within group by performs aggregation by date.
select date(date), sent
select date(date) as Sent Date, sum(sent) as Total Sent group by date(date)
DAYS_IN_MONTH
Length of the month in days (28, 29, 30, 31 etc)
select days_in_month(date), sum(sent) as Total Sent group by days_in_month(date)
DAYS_IN_MONTH
Length of the month in days (28, 29, 30, 31 etc)
select days_in_month(date), sum(sent) as Total Sent group by days_in_month(date)
DAY_OF_WEEK
Day name of the week (Sunday, Monday etc)
select day_of_week(date), sum(sent) as Total Sent group by day_of_week(date)
WEEK
Truncates to a date to the beginning of the week (Sunday). When used within group by performs aggregation by week.
select week(date) as Sent Week, sum(sent) as Total Sent group by week(date)
By default, WEEK(<date-field>) returns the Sunday date at midnight of a given week but with WEEK(<date-field>, offset), you can offset the days and alter the day of the week returned.
Offset Reference:
1 - Monday
2 - Tuesday
3 - Wednesday
4 - Thursday
5 - Friday
6 - Saturday
Example: WEEK(<date-field>, 1): returns the Monday date at midnight of a given week.
WEEK_OF_YEAR
Week Number integer for the input date
select week_of_year(date) as Sent Week, sum(sent) as Total Sent group by week_of_year(date)
NOTES:
1. A week is defined from Monday-Sunday regardless of month or year.
2. All weeks are 7 days long
3. Weeks are not dependent on the month but it is possible to have 5 weeks associated with a day in a month depending on how the days are aligned, For example, if the 1st of a month falls on a Saturday, then that day will belong to the week starting on Monday which falls in the previous month.
4. The first week of a year will always follow the last week in December of the previous year
MONTH
Truncates to the 1st of the month. Aggregates data on a monthly basis when used within group by.
select month(date) as Sent Month, sum(sent) as Total Sent group by month(date)
WEEK_OF_YEAR
Week Number integer for the input date
select week_of_year(date) as Sent Week, sum(sent) as Total Sent group by week_of_year(date)
QUARTER
Truncates to the beginning of the quarter. Aggregates data on a quarterly basis when used within group by.
select quarter(date) as Sent Quarter, sum(sent) as Total Sent group by quarter(date)
YEAR
Truncates to the 1st of the year. Aggregates data on a yearly basis when used within group by.
select year(date) as Sent Quarter, sum(sent) as Total Sent group by year(date)
HOUR
Truncates to the hour for dates with timestamps
select HOUR(date) as Sent Hour, sum(sent) as Total Sent group by hour(date)
MINUTE
Truncates/Groups to the minute for dates with timestamps
select MINUTE(timestamp) as Sent Hour, sum(sent) as Total Sent group by MINUTE(timestamp)
NOW()
Current date/time
select now()
DATE_FORMAT
Converts a date into another format
DATE_FORMAT(<date>,<format>)
select date_format(date,dd-MMM) as Display Format
STR_TO_DATE
Date conversion from a String Converts a string into date using a provided format
STR_TO_DATE(<date>,<format>)
select str_to_date(date,dd-MMM-yy HH:mm) as Converted Date
DATE_ADD
Add a datetime amount to a date
DATE_ADD(<date>,<amount>)
select date_add(date,+1y) as Date
DATE TOKENS
The following reserved tokens enable date queries based on current date/time:
| $c9_now | Current Time |
| $c9_thishour | 00:00 of the Current hour |
| $c9_today | Midnight of the current date |
| $c9_yesterday | Midnight, yesterday |
| $c9_thisweek | Start of the current week (Sunday midnight) |
| $c9_lastweek | Start of last week (Sunday midnight) |
| $c9_thismonth | Midnight of the 1st of the current month |
| $c9_lastmonth | Midnight of the 1st of the last month |
| $c9_thisquarter | Midnight of the 1st of the current quarter (Jan, April, July, Oct) |
| $c9_lastquarter | Midnight of the 1st of the last quarter (Jan, April, July, Oct) |
| $c9_thisyear | Midnight, Jan 1, of the current year |
| $c9_lastyear | Midnight, Jan 1, of the last year |
select * where date > $c9_thisyear
You can also have multiple operations within the date token. For example:
$c9_today-1d+2d
In addition, these can be further manipulated with +/- operands along with time unit identifiers. For example:
select * where date > $c9_thisyear+2m
Gets data from March onwards
select * where date > $c9_yesterday+2h
Data from 2:00 AM yesterday
Time Units
The following are the list of supported time units:
| min | Minutes |
| h | Hours |
| d | Days |
| w | Weeks |
| m | Months |
| q | Quarters |
| y | Years |
TIMEZONES
Default timezone is US/Pacific for date display within Knowi. On-premise agents inherit the server timezone.
Custom Timezones can be set in the query using:
set time_zone=US/Eastern;
Full list of Timezones here.
EPOCH_SECS
Allows for the differentiation between the epoch with millis vs without millis. This is specifically for REST queries
For example:
date={$c9_today,epoch_secs} will format today into epoch seconds format
EPOCH_TO_DATE
Converts an Epoch number of seconds to a readable date format.
For example:
select epoch_to_date(date) as datetime
DATE DELTAS
This calculates the amount of time between two date-times objects to a date/time unit. The result will be a positive whole number, even if the end is before the start.
The calculation returns a whole number, representing the number of complete unites between the two date-times.
To calculate the number of minutes between two date-times:
MINUTES_DELTA(<date field>,<date field>)
select minutes_delta('02/28/2015 22:25:34', '01/28/2015 16:28:34') as minutes_delta;
select minutes_delta(now(), date) as minutes_delta;
Number of hours:
HOURS_DELTA(<date field>,<date field>)
select hours_delta('02/28/2015 22:25:34', '01/28/2015 16:28:34') as hours_delta;
select hours_delta(now(), date) as hours_delta;
Number of days:
DAYS_DELTA(<date field>,<date field>)
select days_delta('02/28/2015 22:25:34', '01/28/2015 16:28:34') as days_delta;
select days_delta(now(), date) as days_delta;
Number of months:
MONTHS_DELTA(<date field>,<date field>)
select months_delta('02/28/2015 22:25:34', '01/28/2015 16:28:34') as months_delta;
select months_delta(now(), date) as months_delta;