Filter by Date
Query with ISO format
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= '2016-06-18T20:00:00Z'
AND datetime < '2016-06-18T21:00:00Z'
| datetime | value |
|--------------------------|-------|
| 2016-06-18T20:00:11.000Z | 28.0 |
| 2016-06-18T20:00:27.000Z | 6.1 |
| 2016-06-18T20:00:43.000Z | 6.1 |
Query with ISO format. Exclude Upper Range
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime BETWEEN '2016-06-18T20:00:00Z'
AND '2016-06-18T20:00:43Z' EXCL
The
EXCLexcludes upper range.
| datetime | value |
|--------------------------|-------|
| 2016-06-18T20:00:11.000Z | 28.0 |
| 2016-06-18T20:00:27.000Z | 6.1 |
Query with Local Format
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= '2016-06-18 20:00:00'
AND datetime < '2016-06-18 21:00:00.000'
| datetime | value |
|--------------------------|-------|
| 2016-06-18T20:00:11.000Z | 28.0 |
| 2016-06-18T20:00:27.000Z | 6.1 |
| 2016-06-18T20:00:43.000Z | 6.1 |
Query with Short Local Format
SELECT datetime, count(value)
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= '2016-06'
AND datetime < '2016-07'
GROUP BY PERIOD(1 DAY)
| datetime | count(value) |
|----------------------|--------------|
| 2016-06-01T00:00:00Z | 5383 |
| 2016-06-02T00:00:00Z | 5378 |
| ... |
| 2016-06-30T00:00:00Z | 5392 |
Query with Lower Limit
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= '2017-04-04T17:07:00Z'
LIMIT 5
| datetime | value |
|----------------------|-------|
| 2017-04-04T17:07:04Z | 0.0 |
| 2017-04-04T17:07:20Z | 2.0 |
| 2017-04-04T17:07:36Z | 7.1 |
| 2017-04-04T17:07:52Z | 90.9 |
| 2017-04-04T17:08:08Z | 3.0 |
Query with Milliseconds
SELECT time, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND time >= 1466100000000
AND time < 1466200000000
| time | value |
|---------------|-------|
| 1466100003000 | 37.2 |
| 1466100019000 | 3.1 |
| 1466100035000 | 4.0 |
Query using Calendar Expressions
Both time and datetime columns support calendar keywords.
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= PREVIOUS_HOUR
| datetime | value |
|--------------------------|-------|
| 2016-06-18T20:00:11.000Z | 28.0 |
| 2016-06-18T20:00:27.000Z | 6.1 |
| 2016-06-18T20:00:43.000Z | 6.1 |
Calendar keywords are calculated based on current server time and time zone.
If the server time zone is Europe/Berlin, for example, current_day in the below query is evaluated to 2017-04-15T00:00:00+02:00 local time or 2017-04-14T22:00:00Z UTC time.
SELECT datetime, date_format(time, 'yyyy-MM-ddTHH:mm:ssZZ') AS local_datetime, value
FROM m1
WHERE datetime >= CURRENT_DAY
| datetime | local_datetime | value |
|----------------------|---------------------------|-------|
| 2017-04-14T22:00:00Z | 2017-04-15T00:00:00+02:00 | 22 | <- midnight in local server time zone: UTC+02:00
| 2017-04-14T23:00:00Z | 2017-04-15T01:00:00+02:00 | 23 |
| 2017-04-15T00:00:00Z | 2017-04-15T02:00:00+02:00 | 0 |
| 2017-04-15T01:00:00Z | 2017-04-15T03:00:00+02:00 | 1 |
| 2017-04-15T02:00:00Z | 2017-04-15T04:00:00+02:00 | 2 |
series e:e1 d:2017-04-14T21:00:00Z m:m1=21
series e:e1 d:2017-04-14T22:00:00Z m:m1=22
series e:e1 d:2017-04-14T23:00:00Z m:m1=23
series e:e1 d:2017-04-15T00:00:00Z m:m1=0
series e:e1 d:2017-04-15T01:00:00Z m:m1=1
series e:e1 d:2017-04-15T02:00:00Z m:m1=2
Query with Calendar Expression Evaluated in Custom Time Zone
The endtime() function allows specifying a user-defined time zone to evaluate calendar keywords and expressions.
The following example selects data between 0h:0m:0s of the previous day and 0h:0m:0s of the current day in PST time zone, even though the database runs in UTC time zone.
SELECT value, datetime,
date_format(time, 'yyyy-MM-ddTHH:mm:ss.SSSZZ', 'UTC') AS "iso_z_dt",
date_format(time, 'yyyy-MM-ddTHH:mm:ssz', 'UTC') AS "UTC_1_dt",
date_format(time, 'yyyy-MM-ddTHH:mm:ssZ', 'UTC') AS "UTC_2_dt",
date_format(time, 'yyyy-MM-ddTHH:mm:ssz', 'US/Pacific') AS "PST_dt"
FROM "cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime BETWEEN endtime(YESTERDAY, 'US/Pacific')
AND endtime(CURRENT_DAY, 'US/Pacific')
ORDER BY datetime
LIMIT 3
| value | datetime | iso_z_dt | UTC_1_dt | UTC_2_dt | PST_dt |
|--------|---------------------------|---------------------------|-------------------------|---------------------------|------------------------|
| 10.2 | 2018-06-11T07:00:09.000Z | 2018-06-11T07:00:09.000Z | 2018-06-11T07:00:09UTC | 2018-06-11T07:00:09+0000 | 2018-06-11T00:00:09PDT |
| 29.17 | 2018-06-11T07:00:25.000Z | 2018-06-11T07:00:25.000Z | 2018-06-11T07:00:25UTC | 2018-06-11T07:00:25+0000 | 2018-06-11T00:00:25PDT |
...
| 9 | 2018-06-12T06:59:31.000Z | 2018-06-12T06:59:31.000Z | 2018-06-12T06:59:31UTC | 2018-06-12T06:59:31+0000 | 2018-06-11T23:59:31PDT |
| 13.4 | 2018-06-12T06:59:47.000Z | 2018-06-12T06:59:47.000Z | 2018-06-12T06:59:47UTC | 2018-06-12T06:59:47+0000 | 2018-06-11T23:59:47PDT |
Query Using Local Time
SELECT datetime as utc_time, date_format(time, 'yyyy-MM-dd HH:mm:ss', 'Europe/Vienna') AS local_datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND time >= date_parse('2017-05-01 12:00:00', 'yyyy-MM-dd HH:mm:ss', 'Europe/Vienna')
AND time < date_parse('2017-05-03 12:00:00', 'yyyy-MM-dd HH:mm:ss', 'Europe/Vienna')
| utc_time | local_datetime | value |
|---------------------|---------------------|--------|
| 2017-05-01 10:00:15 | 2017-05-01 12:00:15 | 4.9500 |
| 2017-05-01 10:00:31 | 2017-05-01 12:00:31 | 3.0000 |
| 2017-05-01 10:00:47 | 2017-05-01 12:00:47 | 3.0900 |
Query Using BETWEEN
The BETWEEN operator matches samples recorded between the start and end of the defined date range. Since BETWEEN is an inclusive operator, the samples recorded at start and end dates are included in the result.
`BETWEEN` is inclusive
datetime BETWEEN t1 AND t2 is equivalent to datetime >= t1 AND datetime <= t2.
To emulate a half-open [) interval, add EXCL instruction to the upper range value.
datetime BETWEEN '2016-06-18T20:00:00Z'
AND '2016-06-18T21:00:00Z' EXCL
As alternative to EXCL, subtract 1 millisecond from the upper range value.
datetime BETWEEN '2016-06-18T20:00:00.000Z'
AND '2016-06-18T20:59:59.999Z'
The above conditions are equivalent to:
datetime >= '2016-06-18T20:00:00Z'
AND
datetime < '2016-06-18T21:00:00Z'
| datetime | value |
|----------------------|-------|
| 2016-06-18T20:00:11Z | 28.0 |
| 2016-06-18T20:00:27Z | 6.1 |
| 2016-06-18T20:00:43Z | 6.1 |
Query Using Rounded Dates
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= date_round(now, 5 MINUTE)
AND datetime < date_round(now + 15*minute, 5 MINUTE)
| utc_time | local_datetime | value |
|---------------------|---------------------|--------|
| 2017-05-01 10:00:15 | 2017-05-01 12:00:15 | 4.9500 |
| 2017-05-01 10:00:31 | 2017-05-01 12:00:31 | 3.0000 |
| 2017-05-01 10:00:47 | 2017-05-01 12:00:47 | 3.0900 |
Query using BETWEEN Subquery
The BETWEEN operator can include a subquery that returns a result set containing multiple rows with one column.
- If the subquery returns no values, the condition evaluates to
false, and no rows are returned. - If the subquery returns only one value, the timestamp of this value determines the lower boundary of the time interval and the upper boundary is not defined.
- If there are two values, the second value must be greater than or equal to the first value.
- If there are more than two values, each pair of values is processed as a separate time interval.
TIP
The intervals in the result set can be identified with the INTERVAL_NUMBER() function.
series d:2017-04-03T01:00:00Z e:nurswgvml007 x:maintenance-rfc=RFC12-start
series d:2017-04-03T01:15:00Z e:nurswgvml007 x:maintenance-rfc=RFC12-stop
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime BETWEEN (
SELECT datetime FROM "maintenance-rfc"
WHERE entity = 'nurswgvml007'
ORDER BY datetime
)
| datetime | value |
|--------------------------|-------|
| 2017-04-03T01:00:09.000Z | 24.0 |
| 2017-04-03T01:00:25.000Z | 55.0 |
...
| 2017-04-03T01:14:17.000Z | 4.0 |
| 2017-04-03T01:14:33.000Z | 4.1 |
| 2017-04-03T01:14:49.000Z | 63.0 |
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime BETWEEN (
SELECT datetime FROM "maintenance-rfc"
WHERE entity = 'nurswgvml007'
ORDER BY datetime
)
| avg(value) | first(value) | last(value) | count(value) |
|------------|--------------|-------------|--------------|
| 14.1 | 24.0 | 63.0 | 56.0 |
Multiple Intervals in the Subquery
-- outer query
WHERE t1.datetime BETWEEN (
SELECT datetime FROM "TV6.Unit_BatchID"
WHERE entity = 'br-1211'
AND (text = '800' OR LAG(text)='800')
)
| datetime |
|----------------------|
| 2016-10-04T02:01:20Z | 1st interval start
| 2016-10-04T02:03:05Z | 1st interval end
| 2016-10-04T02:03:10Z | 2nd interval start
| 2016-10-04T02:07:05Z | 2nd interval end
The above subquery result is equivalent to:
WHERE t1.datetime BETWEEN '2016-10-04T02:01:20Z' AND '2016-10-04T02:03:05Z'
OR t1.datetime BETWEEN '2016-10-04T02:03:10Z' AND '2016-10-04T02:07:05Z'
Query Multiple Intervals with OR
The query can select multiple intervals using the OR operator.
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND (datetime BETWEEN '2017-04-02T14:00:00Z' AND '2017-04-02T14:01:00Z'
OR datetime BETWEEN '2017-04-04T16:00:00Z' AND '2017-04-04T16:01:00Z')
| datetime | value |
|----------------------|-------|
| 2017-04-02T14:00:04Z | 80.8 | start
| 2017-04-02T14:00:20Z | 64.7 |
| 2017-04-02T14:00:36Z | 5.0 |
| 2017-04-02T14:00:52Z | 100.0 | end
| 2017-04-04T16:00:06Z | 54.6 | start
| 2017-04-04T16:00:22Z | 6.0 |
| 2017-04-04T16:00:38Z | 81.0 |
| 2017-04-04T16:00:54Z | 38.8 | end
Query Multiple Intervals with Date Filter
The date filters splits the selection timespan into multiple separate intervals which contain consecutive samples where the date filters evaluated to true.
Each interval opens with the first sample for which the date filter returned true, includes subsequent samples which also evaluate to true, and closes before the first row that returns false.
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime > PREVIOUS_HOUR
AND date_format(time, 'mm:ss') BETWEEN '00:00' AND '00:30'
| datetime | value |
|----------------------|-------|
| 2017-04-04T16:00:06Z | 54.6 |
| 2017-04-04T16:00:22Z | 6.0 |
... Current interval closes when the first sample outside of the [00:00-00:30] time range returns `false`.
... Intermediate samples that evaluate to `false` are not part of any interval.
| 2017-04-04T17:00:08Z | 3.0 | <- New interval starts when new sample evaluates to `true` after previous `false` rows.
| 2017-04-04T17:00:24Z | 3.4 | <- Subsequent `true` rows are part of the interval.
Query to Interpolate Multiple Intervals
Multiple intervals are treated separately for the purpose of interpolating and regularizing values. The values between intervals are neither interpolated nor regularized.
SELECT datetime, value
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND (datetime BETWEEN '2017-04-02T14:00:00Z' AND '2017-04-02T14:01:00Z'
OR datetime BETWEEN '2017-04-04T16:00:00Z' AND '2017-04-04T16:01:00Z')
WITH INTERPOLATE(15 SECOND)
| datetime | value |
|----------------------|-------|
| 2017-04-02T14:00:00Z | 63.6 |
| 2017-04-02T14:00:15Z | 69.7 |
| 2017-04-02T14:00:30Z | 27.4 |
| 2017-04-02T14:00:45Z | 58.4 |
| 2017-04-02T14:01:00Z | 55.1 |
.. No regularized samples are filled between intervals ...
| 2017-04-04T16:00:00Z | 36.8 |
| 2017-04-04T16:00:15Z | 27.3 |
| 2017-04-04T16:00:30Z | 43.5 |
| 2017-04-04T16:00:45Z | 62.5 |
| 2017-04-04T16:01:00Z | 25.4 |
SELECT datetime, AVG(value)
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND (datetime BETWEEN '2017-04-02T14:00:00Z' AND '2017-04-02T14:01:00Z'
OR datetime BETWEEN '2017-04-04T16:00:00Z' AND '2017-04-04T16:01:00Z')
GROUP BY PERIOD(15 SECOND)
| datetime | avg(value) |
|----------------------|------------|
| 2017-04-02T14:00:00Z | 80.8 |
| 2017-04-02T14:00:15Z | 64.7 |
| 2017-04-02T14:00:30Z | 5.0 |
| 2017-04-02T14:00:45Z | 100.0 |
... No intermediate periods are filled between intervals ...
| 2017-04-04T16:00:00Z | 54.6 |
| 2017-04-04T16:00:15Z | 6.0 |
| 2017-04-04T16:00:30Z | 81.0 |
| 2017-04-04T16:00:45Z | 38.8 |
Query by Calendar
Use the date_format or extract function to retrieve parts from a date for the purpose of filtering.
The below query includes only weekdays (Monday through Friday) and daytime hours (08:00 to 17:59).
SELECT datetime, date_format(time, 'eee') AS "day of week", avg(value), count(value)
FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
AND datetime >= previous_week AND datetime < current_week
AND CAST(date_format(time, 'H') AS number) BETWEEN 8 AND 17
AND is_weekday(time, 'USA')
GROUP BY PERIOD(1 hour)
| datetime | day of week | avg(value) | count(value) |
|----------------------|--------------|-------------|--------------|
| 2018-03-12 08:00:00 | Mon | 14.535 | 223 |
| 2018-03-12 09:00:00 | Mon | 12.626 | 225 |
| 2018-03-12 10:00:00 | Mon | 12.114 | 225 |
| 2018-03-12 11:00:00 | Mon | 11.314 | 225 |
| 2018-03-12 12:00:00 | Mon | 18.711 | 223 |
| 2018-03-12 13:00:00 | Mon | 19.721 | 222 |
| 2018-03-12 14:00:00 | Mon | 17.596 | 223 |
| 2018-03-12 15:00:00 | Mon | 12.728 | 225 |
| 2018-03-12 16:00:00 | Mon | 11.646 | 225 |
| 2018-03-12 17:00:00 | Mon | 35.701 | 223 |
| 2018-03-13 08:00:00 | Tue | 10.217 | 224 |
| 2018-03-13 09:00:00 | Tue | 10.033 | 225 |
| 2018-03-13 10:00:00 | Tue | 19.777 | 222 |
| 2018-03-13 11:00:00 | Tue | 14.144 | 225 |
| 2018-03-13 12:00:00 | Tue | 15.393 | 225 |
| 2018-03-13 13:00:00 | Tue | 13.518 | 225 |
| 2018-03-13 14:00:00 | Tue | 10.204 | 225 |
| 2018-03-13 15:00:00 | Tue | 13.378 | 225 |
| 2018-03-13 16:00:00 | Tue | 12.242 | 223 |
| 2018-03-13 17:00:00 | Tue | 56.667 | 224 |
| 2018-03-14 08:00:00 | Wed | 10.793 | 225 |
| 2018-03-14 09:00:00 | Wed | 11.626 | 225 |
| 2018-03-14 10:00:00 | Wed | 11.949 | 225 |
| 2018-03-14 11:00:00 | Wed | 11.437 | 224 |
| 2018-03-14 12:00:00 | Wed | 18.873 | 223 |
| 2018-03-14 13:00:00 | Wed | 12.786 | 225 |
| 2018-03-14 14:00:00 | Wed | 22.585 | 223 |
| 2018-03-14 15:00:00 | Wed | 12.881 | 225 |
| 2018-03-14 16:00:00 | Wed | 27.738 | 202 |
| 2018-03-14 17:00:00 | Wed | 55.621 | 225 |
| 2018-03-15 08:00:00 | Thu | 11.795 | 225 |
| 2018-03-15 09:00:00 | Thu | 10.648 | 225 |
| 2018-03-15 10:00:00 | Thu | 17.217 | 221 |
| 2018-03-15 11:00:00 | Thu | 11.888 | 225 |
| 2018-03-15 12:00:00 | Thu | 11.354 | 225 |
| 2018-03-15 13:00:00 | Thu | 13.089 | 225 |
| 2018-03-15 14:00:00 | Thu | 12.522 | 224 |
| 2018-03-15 15:00:00 | Thu | 10.560 | 225 |
| 2018-03-15 16:00:00 | Thu | 11.245 | 225 |
| 2018-03-15 17:00:00 | Thu | 54.934 | 225 |
| 2018-03-16 08:00:00 | Fri | 12.634 | 224 |
| 2018-03-16 09:00:00 | Fri | 12.510 | 225 |
| 2018-03-16 10:00:00 | Fri | 10.535 | 225 |
| 2018-03-16 11:00:00 | Fri | 10.474 | 225 |
| 2018-03-16 12:00:00 | Fri | 11.605 | 225 |
| 2018-03-16 13:00:00 | Fri | 15.106 | 223 |
| 2018-03-16 14:00:00 | Fri | 10.950 | 225 |
| 2018-03-16 15:00:00 | Fri | 15.914 | 223 |
| 2018-03-16 16:00:00 | Fri | 11.423 | 225 |
| 2018-03-16 17:00:00 | Fri | 52.672 | 224 |
Query by Workday or Weekday
Use IS_WORKDAY or IS_WEEKDAY function to filter holidays, weekdays, and workdays for a specific calendar.
The query below shows averages during observed holidays (non-working weekdays) in the USA.
SELECT date_format(datetime, 'yyyy-MMM-dd') AS "date",
date_format(datetime, 'eee') AS "Day of Week",
AVG(value) AS "Average"
FROM "mpstat.cpu_busy"
WHERE datetime BETWEEN '2018' AND '2019'
AND IS_WEEKDAY(datetime, 'USA')
AND NOT IS_WORKDAY(datetime, 'USA')
GROUP BY PERIOD(1 day)
| date | Day of Week | Average |
|--------------|--------------|---------|
| 2018-Jan-01 | Mon | 12.0 |
| 2018-Jan-15 | Mon | 13.2 |
| 2018-May-28 | Mon | 14.0 |
| 2018-Jul-04 | Wed | 9.7 |
| 2018-Sep-03 | Mon | 20.3 |
| 2018-Nov-12 | Mon | 11.0 |
| 2018-Nov-22 | Thu | 11.4 |
| 2018-Dec-25 | Tue | 14.3 |
The same query for Canada returns fewer observed holidays.
SELECT date_format(datetime, 'yyyy-MMM-dd') AS "date",
date_format(datetime, 'eee') AS "Day of Week",
AVG(value) AS "Average"
FROM "mpstat.cpu_busy"
WHERE datetime BETWEEN '2018' AND '2019'
AND IS_WEEKDAY(datetime, 'CAN')
AND NOT IS_WORKDAY(datetime, 'CAN')
GROUP BY PERIOD(1 day)
| date | Day of Week | Average |
|--------------|--------------|---------|
| 2018-Jan-01 | Mon | 11.2 |
| 2018-Mar-30 | Fri | 12.0 |
| 2018-Sep-03 | Mon | 10.4 |
| 2018-Dec-25 | Tue | 8.1 |
Query by Workday or Weekday in Local Time Zone
If the target calendar time zone differs from the database time zone, specify the time zone explicitly to check exceptions in local time zone.
SELECT date_format(datetime, 'yyyy-MMM-dd HH:mm', 'UTC') AS "date_utc",
date_format(datetime, 'yyyy-MMM-dd HH:mm', 'Asia/Seoul') AS "date_local",
date_format(datetime, 'eee', 'Asia/Seoul') AS "day_of_week_local",
IS_WEEKDAY(datetime, 'kor', 'Asia/Seoul') AS "weekday",
IS_WORKDAY(datetime, 'kor', 'Asia/Seoul') AS "workday",
COUNT(value) AS "Count"
FROM "mpstat.cpu_busy"
WHERE datetime >= date_parse('2018', 'yyyy', 'Asia/Seoul')
AND datetime < date_parse('2019', 'yyyy', 'Asia/Seoul')
AND IS_WEEKDAY(datetime, 'kor', 'Asia/Seoul')
AND NOT IS_WORKDAY(datetime, 'kor', 'Asia/Seoul')
GROUP BY PERIOD(1 day, 'Asia/Seoul')
| date_utc | date_local | day_of_week_local | weekday | workday | Count |
|--------------------|--------------------|--------------------|----------|----------|-------|
| 2017-Dec-31 15:00 | 2018-Jan-01 00:00 | Mon | true | false | 48 |
| 2018-Feb-14 15:00 | 2018-Feb-15 00:00 | Thu | true | false | 48 |
| 2018-Feb-15 15:00 | 2018-Feb-16 00:00 | Fri | true | false | 48 |
| 2018-Feb-28 15:00 | 2018-Mar-01 00:00 | Thu | true | false | 48 |
| 2018-May-06 15:00 | 2018-May-07 00:00 | Mon | true | false | 48 |
| 2018-May-21 15:00 | 2018-May-22 00:00 | Tue | true | false | 48 |
| 2018-Jun-05 15:00 | 2018-Jun-06 00:00 | Wed | true | false | 48 |
| 2018-Jun-12 15:00 | 2018-Jun-13 00:00 | Wed | true | false | 48 |
| 2018-Aug-14 15:00 | 2018-Aug-15 00:00 | Wed | true | false | 48 |
| 2018-Sep-23 15:00 | 2018-Sep-24 00:00 | Mon | true | false | 48 |
| 2018-Sep-24 15:00 | 2018-Sep-25 00:00 | Tue | true | false | 48 |
| 2018-Sep-25 15:00 | 2018-Sep-26 00:00 | Wed | true | false | 48 |
| 2018-Oct-02 15:00 | 2018-Oct-03 00:00 | Wed | true | false | 48 |
| 2018-Oct-08 15:00 | 2018-Oct-09 00:00 | Tue | true | false | 48 |
| 2018-Dec-24 15:00 | 2018-Dec-25 00:00 | Tue | true | false | 48 |
If time zone is not specified, the IS_WORKDAY checks the date in server time zone.
SELECT date_format(datetime, 'yyyy-MM-dd HH:mm') AS "date_utc",
date_format(datetime, 'yyyy-MM-dd HH:mm', 'US/Pacific') AS "date_local",
date_format(datetime, 'eee') AS "day_of_week",
date_format(datetime, 'eee', 'US/Pacific') AS "day_of_week_local",
is_workday(datetime, 'USA') AS "workday_utc",
is_workday(datetime, 'USA', 'US/Pacific') AS "workday_local"
FROM "mpstat.cpu_busy"
WHERE datetime >= date_parse('2018-07-03 14:00', 'yyyy-MM-dd HH:mm', 'US/Pacific')
AND datetime < date_parse('2018-07-04 04:00', 'yyyy-MM-dd HH:mm', 'US/Pacific')
GROUP BY PERIOD(1 hour)
ORDER BY datetime
| date_utc | date_local | day_of_week | day_of_week_local | workday_utc | workday_local |
|-------------------|-------------------|--------------|--------------------|--------------|---------------|
| 2018-07-03 21:00 | 2018-07-03 14:00 | Tue | Tue | true | true |
| 2018-07-03 22:00 | 2018-07-03 15:00 | Tue | Tue | true | true |
| 2018-07-03 23:00 | 2018-07-03 16:00 | Tue | Tue | true | true |
| 2018-07-04 00:00 | 2018-07-03 17:00 | Wed | Tue | false (!) | true |
| 2018-07-04 01:00 | 2018-07-03 18:00 | Wed | Tue | false (!) | true |
| 2018-07-04 02:00 | 2018-07-03 19:00 | Wed | Tue | false (!) | true |
| 2018-07-04 03:00 | 2018-07-03 20:00 | Wed | Tue | false (!) | true |
| 2018-07-04 04:00 | 2018-07-03 21:00 | Wed | Tue | false (!) | true |
| 2018-07-04 05:00 | 2018-07-03 22:00 | Wed | Tue | false (!) | true |
| 2018-07-04 06:00 | 2018-07-03 23:00 | Wed | Tue | false (!) | true |
| 2018-07-04 07:00 | 2018-07-04 00:00 | Wed | Wed | false | false |
| 2018-07-04 08:00 | 2018-07-04 01:00 | Wed | Wed | false | false |
| 2018-07-04 09:00 | 2018-07-04 02:00 | Wed | Wed | false | false |
| 2018-07-04 10:00 | 2018-07-04 03:00 | Wed | Wed | false | false |
Query using Workday Offset
The WORKDAY function allows shifting of the input date by the specified number of working days.
SELECT value,
date_format(time, 'EEE yyyy-MMM-dd HH:mm:ss') AS "T-0",
date_format(workday(time, -1, 'usa'), 'EEE yyyy-MMM-dd HH:mm:ss') as "T-1",
date_format(workday(time, -2, 'usa'), 'EEE yyyy-MMM-dd HH:mm:ss') as "T-2"
FROM "mpstat.cpu_busy"
WHERE datetime between '2019-07-08 15:00:00' and '2019-07-08 15:10:00'
AND entity = 'nurswgvml007'
LIMIT 1
| value | T-0 | T-1 | T-2 |
|-------|--------------------------|--------------------------|--------------------------|
| 63.27 | Mon 2019-Jul-08 15:00:10 | Fri 2019-Jul-05 15:00:10 | Wed 2019-Jul-03 15:00:10 |