Dates functions

In the case that your data is not detected as date, you need to first apply the Convert to date function to the column with dates before applying any other date-related function. The format information that you need to fill is just to configure the display text format of the date, not to parse a text not detected as date.

新增持續時間

Adds duration to a date, time, or timestamp, in the specified time unit.

屬性

屬性	設定
要處理的欄	Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.
時間單位	Select which part of the date, time, or timestamp you want to add time to: 年月週日時分秒毫秒微秒
使用搭配	Value: Select this option to add a fixed value, set in the Value field. Other column: Select this option to add a value from another column, set in the Column drop-down list.
建立新欄	Select this check box if you want to output the result of this function in a new column and keep the original untouched.

範例

輸入	設定	輸出
03/03/2023	Time unit: Year Use with: Value Value: 10	03/03/2033
3/7/2019 2:00:00 PM	Time unit: Seconds Use with: Value Value: 30	3/7/2019 2:00:30 PM

計算日期差異

For a given time part unit, calculates the time between the dates in your column and the date of your choice.

計算的方法是先將日期截斷為指定的日期或時間部分，然後以整數形式傳回兩個時間部分之間的差距。

屬性

屬性	設定
要處理的欄	Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.
時間單位	Select the time part to use for the calculation: 年月日時分秒毫秒微秒
直到	Select how you want to set the date to target: Now, to calculate to difference until the current date and time. Specific date, to enter the date of your choice, set using the date picker in the This date field. Other column to use dates from another column, set in the Column drop-down list.
建立新欄	Select this check box if you want to output the result of this function in a new column and keep the original untouched.

範例

輸入	設定	輸出
06/12/1989	Time unit: Day Until: Specific date This date: 31/12/2000	4044
Column A: 8/4/2016 Column B: 8/4/2025	Time until: Week Until: Other column Column: B	468

比較日期

Compares the dates in the column with a specific value, or values from another column. The function will return TRUE if the condition set by the operator is met, otherwise it will return FALSE.

Times can only be compared with times but timestamps and dates can be compared. In this case, a date will be interpreted as a timestamp with time set to 00:00.

屬性

屬性	設定
要處理的欄	Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.
比較模式	Select the operator that will be used to compare the dates. 等於不等於之後之後或等於之前之前或等於
使用搭配	Value: Select this option to check the column against a fixed value, set with the Value date picker. Other column: Select this option to check the column against values from another column, set in the Column drop-down list.
建立新欄	Select this check box if you want to output the result of this function in a new column and keep the original untouched.

範例

輸入	設定	輸出
1969-02-27 1989-11-09	Compare mode: After Use with: Value Value: 01/01/1989	FALSE TRUE

轉換為日期

Converts the type of data contained in the column so that it is interpreted as dates in the system.

屬性

設定

要處理的欄

Select the column or columns on which you want to apply the function.

If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.

日期格式

Select the expected date format for the column.

Auto, to automatically detect the current format.
Custom, to enter your specific date format, set in the Date pattern field.
YYYY-MM-DD
YYYYMMDD
M/D/YYYY
M/D/YY
MM/DD/YY
MMM D, YYYY
MMMM D, YYYY

建立新欄

Select this check box if you want to output the result of this function in a new column and keep the original untouched.

轉換為時間

Converts the type of data contained in the column so that it is interpreted as time in the system.

屬性

設定

要處理的欄

Select the column or columns on which you want to apply the function.

If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.

日期格式

Select the expected time format for the column.

Auto, to automatically detect the current format.
Custom, to enter your specific time format, set in the Date pattern field.
hh:mm
hh:mm:ss
hh:mm AM/PM
hh:mm:ss AM/PM

建立新欄

Select this check box if you want to output the result of this function in a new column and keep the original untouched.

轉換為時間戳記

Converts the type of data contained in the column so that it is interpreted as timestamp in the system.

屬性

設定

要處理的欄

Select the column or columns on which you want to apply the function.

If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.

日期格式

Select the expected timestamp format for the column.

Auto, to automatically detect the current format.
Custom, to enter your specific timestamp format, set in the Date pattern field.
YYYYMMDD hh:mm
YYYYMMDD hh:mm:ss
YYYYMMDDThh:mm
YYYYMMDDThh:mm:ss
M/D/YYYY hh:mm AM/PM
M/D/YY hh:mm AM/PM
MM/DD/YY hh:mm AM/PM
M/D/YYYY hh:mm:ss AM/PM
M/D/YY hh:mm:ss AM/PM
MM/DD/YY HH:mm:ss AM/PM
MMM D, YYYY hh:mm:ss AM/PM
Weekday, MMMM D, YYYY hh:mm:ss AM/PM

建立新欄

Select this check box if you want to output the result of this function in a new column and keep the original untouched.

建立起始日期部分

Creates a new date column by concatenating numeric parts from other columns.

屬性

屬性	設定
Year (年)	Select the column that contains the information with the year for the date you want to create.
月	Select the column that contains the information with the month for the date you want to create.
日	Select the column that contains the information with the day for the date you want to create.
新的欄名稱	Enter the name of the column that will be created for the concatenation. If you do not enter a new name, the default column name will be new_date.

範例

輸入	設定	輸出
Column A: 10 Column B: 2021 Column C: 12	Year: Column B Month: Column A Day: Column C	Column D: 10/21/2021

建立起始時間部分

Creates a new time column by concatenating numeric parts from other columns.

屬性

屬性	設定
小時	Select the column that contains the information with the hours for the time you want to create.
分鐘	Select the column that contains the information with the minutes for the time you want to create.
秒	Select the column that contains the information with the seconds for the time you want to create.
毫秒	Select the column that contains the information with the milliseconds for the time you want to create.
新的欄名稱	Enter the name of the column that will be created for the concatenation. If you do not enter a new name, the default column name will be new_time.

範例

輸入	設定	輸出
Column A: 9 Column B: 1 Column C: 30 Column D: 56	Hour: Column A Minute: Column D Second: Column C Millisecond: Column B	Column E: 09:56:30.100

建立起始時間戳記部分

Creates a new timestamp column by concatenating numeric parts from other columns.

屬性

屬性	設定
Year (年)	Select the column that contains the information with the year for the timestamp you want to create.
月	Select the column that contains the information with the month for the timestamp you want to create.
日	Select the column that contains the information with the day for the timestamp you want to create.
小時	Select the column that contains the information with the hours for the timestamp you want to create.
分鐘	Select the column that contains the information with the minutes for the timestamp you want to create.
秒	Select the column that contains the information with the seconds for the timestamp you want to create.
毫秒	Select the column that contains the information with the milliseconds for the timestamp you want to create.
新的欄名稱	Enter the name of the column that will be created for the concatenation. If you do not enter a new name, the default column name will be new_timestamp.

範例

輸入	設定	輸出
Column A: 10 Column B: 2021 Column C: 12 Column D: 9 Column E: 1 Column F: 30 Column G: 56	Year: Column B Month: Column A Day: Column C Hour: Column D Minute: Column G Second: Column F Millisecond: Column E	Column H: 10/12/2021 9:56:30 AM

擷取日期部分

Extracts parts of a date and creates separate columns with the selected date parts.

屬性

設定

要處理的欄

Select the column or columns on which you want to apply the function.

If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.

AM/PM
日
日子名稱
星期幾
一年中某天
小時 (12 小時制)
小時 (24 小時制)
分鐘
月
月份名稱
季度
季數
秒
週
Year (年)
年-月
年-季
年-週

Select which columns will be created using the toggles. Each selected part will be extracted in a different column. The default column name will be <prefix_or_source_column_name>_<date_part>.

日期部分前置詞

Enter a custom prefix that will be added to the name of each created column. If you do not specify a prefix, the name of the source column with the dates will be used as prefix.

範例

輸入	設定	輸出
10/12/2021 9:56:30 AM	日子名稱星期幾月份名稱季度週 Date part prefix: extracted	extracted_dayLabel: Thu extracted_dayOfWeek: 4 extracted_monthLabel: Oct extracted_quarterLabel: Q4 extracted_weekOfYear: 43

格式日期

Changes the date format to use in a date column.

屬性

設定

要處理的欄

Select the column or columns on which you want to apply the function.

If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.

日期格式

Select one of the predefined formats from the drop-down list or enter a custom one to apply on the dates:

Custom, to enter your specific date format, set in the Date pattern field.
YYYY-MM-DD
YYYYMMDD
M/D/YYYY
M/D/YY
MM/DD/YY
MMM D, YYYY
MMMM D, YYYY
YYYYMMDD hh:mm
YYYYMMDD hh:mm:ss
YYYYMMDDThh:mm
YYYYMMDDThh:mm:ss
M/D/YYYY hh:mm AM/PM
M/D/YY hh:mm AM/PM
MM/DD/YY hh:mm AM/PM
M/D/YYYY hh:mm:ss AM/PM
M/D/YY hh:mm:ss AM/PM
MM/DD/YY HH:mm:ss AM/PM
MMM D, YYYY hh:mm:ss AM/PM
Weekday, MMMM D, YYYY hh:mm:ss AM/PM
hh:mm
hh:mm:ss
hh:mm AM/PM
hh:mm:ss AM/PM

建立新欄

Select this check box if you want to output the result of this function in a new column and keep the original untouched.

範例

輸入	設定	輸出
12-31-2000	Date format: Custom Date pattern: DD/MM/YYYY	31/12/2000
05/04/2017	Date format: Weekday, MMMM D, YYYY hh:mm:ss AM/PM	Thursday, May 4, 2017 12:00:00 am

截斷日期

Removes part of a date. The date or timestamp will be set to the first point of the unit to which you are truncating.

屬性

設定

要處理的欄

Select the column or columns on which you want to apply the function.

If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply.

時間單位

Select the part of the date after which the value will be truncated. If truncating to Day or higher, the value returned will be a date, otherwise, it will be a timestamp.

建立新欄

Select this check box if you want to output the result of this function in a new column and keep the original untouched.

範例

輸入	設定	輸出
2024-06-22	Time unit: Year	2024-01-01
2024-06-22 11:05	Time unit: Month	2024-06-01
2024-06-22 11:05:35	Time unit: Second	2024-06-22 11:05:01

此頁面是否對您有幫助？

若您發現此頁面或其內容有任何問題——錯字、遺漏步驟或技術錯誤——請告知我們！

在此留下意見回饋