Date Operations

Date operations let you truncate, extract, format, convert and calculate with dates. Cloud9QL automatically attempts to parse various date formats, so most date fields work without any conversion. If a format is not recognized, convert it first with str_to_date(<date>,<format>).

• Truncate a date to a unit of time. Returns a date, and is useful for grouping: DATE, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE
• Extract part of a date as a number or name: DAY_OF_WEEK, DAY_OF_MONTH, DAYS_IN_MONTH, WEEK_OF_YEAR, MONTH_OF_YEAR, HOUR_OF_DAY, MINUTE_OF_DAY
• Format and convert dates: DATE_FORMAT (returns text), STR_TO_DATE, EPOCH_TO_DATE
• Calculate with dates: NOW, DATE_ADD, and the date deltas (MINUTES_DELTA, HOURS_DELTA, DAYS_DELTA, MONTHS_DELTA)
• Query relative dates with date tokens such as $c9_today, and control display with timezones 

Quick links to date operators:

• DATE: Truncates a date to midnight.
• DAY_OF_WEEK: Returns the name of the day of the week (Sunday, Monday, etc.).
• DAY_OF_MONTH: Returns the day of the month (1-31).
• DAYS_IN_MONTH: Returns the number of days in a month (28, 29, 30, or 31).
• 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.
• MONTH_OF_YEAR: Returns the month of the year as a number (1-12).
• 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.
• HOUR_OF_DAY: Returns the hour of the day as an integer (0-23).
• MINUTE: Truncates a date with timestamps to the minute.
• MINUTE_OF_DAY: Returns the number of minutes since midnight (0-1439).
• NOW: Returns the current date and time.
• DATE_FORMAT: Converts a date into a formatted text string.
• STR_TO_DATE: Converts a string into a date using a provided format.
• DATE_ADD: Adds an amount of time to a date, or subtracts it when the amount is negative.
• 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 the timezone that date functions and date tokens use within a query. Use DATE_FORMAT to display a date in that timezone.
• EPOCH_SECS: Formats a date token as epoch seconds in REST API datasource requests, for example {$c9_today:epoch_secs}.
• EPOCH_TO_DATE: Converts an epoch number (in seconds or milliseconds) to a date.
• DATE DELTAS: Calculates the number of whole minutes, hours, days or months between two dates.

DATE

Truncates a date to midnight (00:00:00). Returns a date. When used within group by, aggregates data by day.

DATE(<date field>)

select date(date), sent
select date(date) as Sent Date, sum(sent) as Total Sent group by date(date)

DAY_OF_WEEK

Returns the name of the day of the week (Sunday, Monday, etc.) as text.

DAY_OF_WEEK(<date field>)

select day_of_week(date), sum(sent) as Total Sent group by day_of_week(date)

DAY_OF_MONTH

Returns the day of the month as a number (1 to 31).

DAY_OF_MONTH(<date field>)

select day_of_month(date), sum(sent) as Total Sent group by day_of_month(date)

DAYS_IN_MONTH

Returns the number of days in the month of a date as a number (28, 29, 30 or 31).

DAYS_IN_MONTH(<date field>)

select days_in_month(date), sum(sent) as Total Sent group by days_in_month(date)

WEEK

Truncates a date to the start of its week (Sunday at midnight). Returns a date. When used within group by, aggregates data by week.

WEEK(<date field>[,<offset>])

select week(date) as Sent Week, sum(sent) as Total Sent group by week(date)

To return a different day of the week, add an offset. The offset is the number of days added to the Sunday returned by default:

For example, this returns the Monday at midnight of the week:

select week(date,1) as Sent Week

WEEK_OF_YEAR

Returns the week number of the year for a date as a number (1 to 53).

WEEK_OF_YEAR(<date field>)

select week_of_year(date) as Sent Week, sum(sent) as Total Sent group by week_of_year(date)

Notes:

• Weeks follow ISO 8601. A week runs from Monday to Sunday, and all weeks are 7 days long. This differs from WEEK, which starts each week on Sunday.
• Week 1 is the week that contains January 4th. The first days of January can belong to week 52 or 53 of the previous year, and the last days of December can belong to week 1 of the next year.
• Weeks do not depend on the month, so a single week can span two months.

MONTH

Truncates a date to midnight on the 1st of the month. Returns a date. When used within group by, aggregates data by month.

MONTH(<date field>)

select month(date) as Sent Month, sum(sent) as Total Sent group by month(date)

MONTH_OF_YEAR

Returns the month of the year as a number (1 to 12).

MONTH_OF_YEAR(<date field>)

select month_of_year(date), sum(sent) as Total Sent group by month_of_year(date)

QUARTER

Truncates a date to midnight on the 1st day of its quarter (Jan 1, Apr 1, Jul 1 or Oct 1). Returns a date. When used within group by, aggregates data by quarter.

QUARTER(<date field>)

select quarter(date) as Sent Quarter, sum(sent) as Total Sent group by quarter(date)

YEAR

Truncates a date to midnight on January 1st of its year. Returns a date. When used within group by, aggregates data by year.

YEAR(<date field>)

select year(date) as Sent Year, sum(sent) as Total Sent group by year(date)

HOUR

Truncates a date to the start of the hour (minutes and seconds set to 0). Returns a date. Useful for dates with timestamps. When used within group by, aggregates data by hour.

HOUR(<date field>)

select hour(date) as Sent Hour, sum(sent) as Total Sent group by hour(date)

HOUR_OF_DAY

Returns the hour of the day as a number from 0 to 23, rather than a truncated date. Useful for grouping or filtering by time of day across multiple dates.

HOUR_OF_DAY(<date field>)

select hour_of_day(date) as Hour, sum(sent) as Total Sent group by hour_of_day(date)

MINUTE

Truncates a date to the start of the minute (seconds set to 0). Returns a date. Useful for dates with timestamps. When used within group by, aggregates data by minute.

MINUTE(<date field>)

select minute(date) as Sent Minute, sum(sent) as Total Sent group by minute(date)

MINUTE_OF_DAY

Returns the number of minutes since midnight as a number from 0 to 1439, rather than a truncated date. Useful for grouping or filtering by minute of the day across multiple dates.

MINUTE_OF_DAY(<date field>)

select minute_of_day(date) as Minute of Day, sum(sent) as Total Sent group by minute_of_day(date)

NOW

Returns the current date and time. Returns a date.

NOW()

select now()

DATE_FORMAT

select date_format(date,dd-MMM) as Display Format

Options:

DATE_ADD

Adds an amount of time to a date, or subtracts it when the amount is negative. Returns a date. The amount is a number with a + or - sign followed by a time unit, such as +1d, -2h or +1y.

DATE_ADD(<date field>,<amount>)

select date_add(date,+1y) as Next Year
select date_add(date,-7d) as Week Earlier
select date_add(date,+30min) as Plus 30 Minutes

STR_TO_DATE

Converts a text string into a date using the provided format. Returns a date. The format describes how the date is written in the string, using the same letters as DATE_FORMAT.

STR_TO_DATE(<date field>,<format>)

select str_to_date(date,dd-MMM-yy HH:mm) as Converted Date

DATE TOKENS

The following reserved tokens enable date queries based on current date/time:

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

Gets data from March onwards

select * where date > $c9_yesterday+2h

Data from 2:00 AM yesterday

TIME UNITS

Time units are used in date token arithmetic (for example $c9today-1d), DATE_ADD and time moving averages. The following time units are supported:

EPOCH_SECS

Formats a date token as epoch seconds instead of epoch milliseconds (use epoch for milliseconds). This is not used in a Cloud9QL query. It is a format for date tokens wrapped in curly brackets, which are resolved in the URL, URL parameters, headers and POST payload of a REST API datasource. The same tokens are also resolved in a few other places, such as report export names.

{<date token>:epoch_secs}

https://example.com/api/events?since={$c9_today:epoch_secs}

See the REST API documentation for more on using date tokens in REST API datasources.

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.

set time_zone changes the timezone that date functions and date tokens (such as DATE_FORMAT, DATE_ADD and $c9_today) use for the rest of the query. It does not convert date columns that are simply selected. A plain select date still displays in the default timezone.

To display a date in another timezone, wrap it in DATE_FORMAT. Include z in the format to show the timezone abbreviation. See the DATE_FORMAT options for all available format letters.

DATE_FORMAT(<date>,<format>)

Functions that return a date value, such as DATE(), calculate in the timezone you set (for example, the start of the day in US/Eastern), but the returned value is still displayed in the default timezone. Use DATE_FORMAT when you need the result displayed in the set timezone.

Example, comparing the original date to the same date displayed in US/Eastern:

set time_zone=US/Eastern;
select date as Original, date_format(date, MM/dd/yyyy HH:mm:ss z) as Eastern;

EPOCH_TO_DATE

Converts an epoch number (in seconds or milliseconds) to a date. Returns a date.

EPOCH_TO_DATE(<field>)

select epoch_to_date(date) as Converted Date

DATE DELTAS

Calculates the number of complete time units between two dates. The result is a positive whole number, even if the end date is before the start date. Partial units are dropped, so 11 hours apart is 0 days.

Minutes between two dates:

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;

Hours between two dates:

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;

Days between two dates:

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;

Months between two dates:

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;