transformations:makedatetimecolumns
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| transformations:makedatetimecolumns [2021/04/02 04:33] – dmitry | transformations:makedatetimecolumns [2021/07/19 02:24] (current) – [Examples] 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===== | ||
| + | **Example: | ||
| + | |||
| + | **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 table:** | ||
| ^ 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 85: | ||
| | 2020-Nov-30 | | 2020-Nov-30 | ||
| - | ** See also ** | + | \\ |
| - | * [[transformations:convert|Convert Data Types]] | + | ====Community examples==== |
| - | * [[transformations:calendar|Calendar]] | + | * [[https:// |
| - | * [[syntax:functions# | + | * [[https:// |
| + | \\ | ||
| + | =====See also===== | ||
| + | * [[transformations: | ||
| + | * [[syntax: | ||
| + | * [[syntax: | ||
transformations/makedatetimecolumns.txt · Last modified: 2021/07/19 02:24 by craigt