transformations:makedatetimecolumns
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | Next revisionBoth sides next revision | ||
transformations:makedatetimecolumns [2021/04/02 04:33] – dmitry | transformations:makedatetimecolumns [2021/04/23 18:17] – craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Make Date/Time Columns | + | {{ transformations: |
+ | ====== MAKE DATE/TIME COLUMNS | ||
+ | Category: Transform / Advanced \\ | ||
- | Take a column with dates and produce one or several | + | \\ |
+ | =====Description===== | ||
+ | Take a column with dates and/or times and produce one or more columns | ||
- | Column that can be created: | + | \\ |
- | * Formatted | + | =====Use cases===== |
- | * Year | + | This action is useful for breaking dates and times into components for downstream date/time calculations: |
- | * Quarter (e.g. Q1) | + | * Calculating " |
- | * Year-quarter (e.g. 2019-Q1) | + | * Calculating " |
- | * Month name | + | * Finding the days' or years' difference between two dates. |
- | * Year-month (e.g. 2019-Apr) | + | * Finding the hours' or minutes' |
- | * Day | + | * Counting down "days remaining" |
- | * Week day | + | |
- | * Day of year | + | |
- | * ISO week number | + | |
- | * Month start | + | |
- | * Month end | + | |
- | * Week start | + | |
- | * Week end | + | |
- | * Hour (12-hour) | + | |
- | * Hour (24-hour) | + | |
- | * AM/PM | + | |
- | * Minutes | + | |
- | * Seconds | + | |
- | * Milliseconds | + | |
- | **Example** | + | \\ |
+ | =====Action settings===== | ||
+ | ^Setting^Description^ | ||
+ | |Column|Select the column containing date/time values to derive the individual date/time components from.| | ||
+ | \\ | ||
+ | ====Date components==== | ||
+ | ^Option^Description^ | ||
+ | |Formatted date|Creates a date value in the format provided (enter a valid date format). See [[syntax: | ||
+ | |Year|Outputs the numeric 4-digit year (e.g. " | ||
+ | |Quarter|Outputs the quarter as a string value (e.g. " | ||
+ | |Year-quarter|Outputs the year and quarter as a string value (e.g., " | ||
+ | |Month name|Outputs the 3-letter month abbreviation (e.g., " | ||
+ | |Year-month|Ouptuts the year and abbreviated month name (e.g., " | ||
+ | |Day|Outputs the numeric day number (e.g., 5).| | ||
+ | |Week day|Outputs the 3-letter day abbreviation (e.g., " | ||
+ | |Day of year|Outputs the numeric sequential day number within the year (e.g., " | ||
+ | |ISO week number|Outputs the numeric sequential week number within the year (e.g., " | ||
+ | |Month start|Outputs the fully-formatted date value of the first day of the month (e.g., " | ||
+ | |Month end|Outputs the fully-formatted date value of the last day of the month (e.g., " | ||
+ | |Week start|Outputs the fully-formatted date value of the first day of the week the source date falls in (e.g., " | ||
+ | |Week end|Outputs the fully-formatted date value of the last day of the week the source date falls in (e.g., " | ||
+ | \\ | ||
+ | ====Month, Year-month, Week day output format options==== | ||
+ | ^Option^Description^ | ||
+ | |English|Outputs the text value in English.| | ||
+ | |Local|Outputs the text value in the local language based on your system settings.| | ||
+ | |Number|Outputs the value as a number instead of text.| | ||
+ | \\ | ||
+ | ====Time components==== | ||
+ | ^Option^Description^ | ||
+ | |Hour (12-hour)|Outputs the hour as a numeric value based on a 12-hour clock (e.g., 1 to 12).| | ||
+ | |Hour (24-hour)|Outputs the hour as a numeric value based on a 24-hour clock (e.g., 0-23).| | ||
+ | |AM/ | ||
+ | |Minutes|Outputs the minutes as a numeric value (e.g. 0-59).| | ||
+ | |Seconds|Outputs the seconds as a numeric value (e.g., 0-59). | | ||
+ | |Milliseconds|Outputs the milliseconds as a numeric value (e.g., 0-999). | | ||
- | Table before: | + | \\ |
+ | =====Remarks===== | ||
+ | Dates without time values are calculated based on a default time of 12:00 AM.\\ | ||
+ | |||
+ | The options in this action only work on values stored in the date/time format, not on dates or times stored as text values (which will produce < | ||
+ | |||
+ | **How to determine if values are actually date/times, or being stored as text**\\ | ||
+ | There are several ways to confirm values // | ||
+ | * __Cell alignment: | ||
+ | * __The Column Profile/ | ||
+ | * __Analysis View, Table metadata:__ Date/time column values will show counts under " | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | \\ | ||
+ | =====Examples===== | ||
+ | **Objective: | ||
+ | |||
+ | **Source table:** | ||
^ Date ^ | ^ Date ^ | ||
| 2020-Nov-28 | | 2020-Nov-28 | ||
| 2020-Nov-29 | | 2020-Nov-29 | ||
| 2020-Nov-30 | | 2020-Nov-30 | ||
- | + | \\ | |
- | Objective: create columns with year-quarter, | + | **Action parameters:** |
- | + | > Column is " | |
- | Table after: | + | > Select options "Year-quarter", " |
+ | \\ | ||
+ | **Result:** | ||
^ Date ^ Year-quarter ^ Week day ^ ISO Week number ^ | ^ Date ^ Year-quarter ^ Week day ^ ISO Week number ^ | ||
| 2020-Nov-28 | | 2020-Nov-28 | ||
Line 41: | Line 88: | ||
| 2020-Nov-30 | | 2020-Nov-30 | ||
- | ** See also ** | + | \\ |
- | * [[transformations: | + | =====See also===== |
- | * [[transformations:calendar|Calendar]] | + | * [[transformations: |
- | * [[syntax: | + | * [[syntax:functions# |
+ | * [[syntax: |
transformations/makedatetimecolumns.txt · Last modified: 2021/07/19 02:24 by craigt