Time Components
Many data sets contain date and datetime fields. Transaction date for Sales Orders and Measurement date for IoT data streams are just a few examples. These are often too granular to observe patterns. Hence, higher level time components such as year and month are extracted out of these granular timestamps and then the data is summarized. SQL Frames provides time API to solve several of these use cases.
Time components can be absolute or relative.
Absolute time components
These components are unique across the time dimension.
| API | Comments |
|---|---|
| year(field) | Year of the date field |
| quarter(field) | Calendar Quarter of the date field |
| month(field) | Calendar Month of the date field |
| date(field) | Date of the date field without hours/minutes/seconds |
| week(field) | ISO Week of the date field |
Relative time components
These components are unique within a specific time frame such as a year or week.
| API | Comments |
|---|---|
| quarterOfYear(field) | Quarter of the date field within the Year |
| monthOfYear(field) | Month of the date field within the year |
| weekOfYear(field) | ISO Week of the date field within the year |
| dayOfYear(field) | Day of the date field within the year |
| dayOfMonth(field) | Day of the date field within the month |
| dayOfWeek(field) | Day of the date field within the ISO week |
| hour(field) | Hour of the date field |
| hourOfDay(field) | Hour of the date field within the day |
| minuteOfHour(field) | Minute of the date time field within the hour |
PIVOT
Using both absolute time components and relative components, it is possible to display the summarized data as a PIVOT table to perform trend analysis. For example, year over year growth in each quarter.
Reference
Below data shows dates for one year into the past and one year into the future. The present and past are visualally distinguished using conditional formatting. This data should help understand the various time components.