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:00.000Z'
| 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 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 with End Time Syntax

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   |

Query with End Time Syntax

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-dd''T''HH: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 End Time Syntax in Custom Time Zone

With the endtime() function, specify 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 server runs in UTC time zone.

SELECT value, datetime,
  date_format(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSZZ', 'UTC') AS "iso_z_dt",
  date_format(time, 'yyyy-MM-dd''T''HH:mm:ssz', 'UTC') AS "UTC_1_dt",
  date_format(time, 'yyyy-MM-dd''T''HH:mm:ssZ', 'UTC') AS "UTC_2_dt",
  date_format(time, 'yyyy-MM-dd''T''HH: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 selects samples recorded between the start and end of the defined interval. BETWEEN is an inclusive operator, thus the interval start and end dates are included in the result.

The expression datetime BETWEEN t1 and t2 is equivalent to datetime >= t1 and datetime <= t2.

To emulate a half-open [) interval subtract 1 millisecond from the AND value.

SELECT datetime, value
  FROM "mpstat.cpu_busy"
WHERE entity = 'nurswgvml007'
  AND datetime BETWEEN '2016-06-18T20:00:00.000Z' AND '2016-06-18T20:59:59.999Z'

The above condition is equivalent to:

datetime >= '2016-06-18T20:00:00.000Z' AND datetime < '2016-06-18T21:00:00.000Z'
| 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 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         |