This guide provides quick examples of useful date functions, like truncating dates, calculating time differences, and converting between formats.
Cloud9QL automatically tries to parse different date formats, but if a format isn’t supported, you can use str_to_date(<date>, <format>)
to convert it manually. Click here to see all your options for date formatting.
QUICK LINKS TO DATE OPERATORS:
- DATE: Truncates a date to midnight.
- DAYS_IN_MONTH: Returns the number of days in a month (28, 29, 30, or 31).
- DAY_OF_WEEK: Returns the name of the day of the week (Sunday, Monday, etc.).
- WEEK: Truncates a date to the beginning of the week (Sunday); supports offsets.
- WEEK_OF_YEAR: Returns the week number of the year for a given date.
- MONTH: Truncates a date to the first of the month.
- QUARTER: Truncates a date to the beginning of the quarter.
- YEAR: Truncates a date to the first of the year.
- HOUR: Truncates a date with timestamps to the hour.
- MINUTE: Truncates a date with timestamps to the minute.
- NOW(): Returns the current date and time.
- DATE_FORMAT: Converts a date into another format.
- STR_TO_DATE: Converts a string into a date using a provided format.
- DATE_ADD: Adds a datetime value (e.g., days, months, years) to a date.
-
DATE TOKENS: Predefined date-based tokens like
$c9_today
,$c9_thisweek
, etc. - TIME UNITS: Supported units for time intervals (min, h, d, w, m, q, y).
- TIMEZONES: Sets or uses the default timezone for date display.
- EPOCH_SECS: Converts the date to epoch seconds format.
- EPOCH_TO_DATE: Converts an epoch time to a readable date.
- DATE DELTAS: Calculates time differences between two dates (minutes, hours, days, months).
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)
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)
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)
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 |
Date tokens can be used in Cloud9QL queries. Here are some examples:
select * where date > $c9_thisyear
You can also have multiple operations within the date token. For example:
$c9_today-1d+2h
In addition, these can be further manipulated with +/- operands along with time unit identifiers. For example:
select * where date > $c9_thisyear+2m
Includes data from March onwards for the current year
select * where date > $c9_yesterday+2h
Includes data from 2:00 AM yesterday and onwards
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 supported 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;