The format defined replaces the date and time formats of the operating system (regional settings).
Syntax:
TimestampFormat
The following examples use 1983-12-14T13:15:30Z as timestamp data to show the results of different SET TimestampFormat statements. The date format used is YYYYMMDD and the time format is h:mm:ss TT. The date format is specified in the SET DateFormat statement and the time format is specified in the SET TimeFormat statement, at the top of the data load script.
| Example | Result |
|---|---|
|
SET TimestampFormat='YYYYMMDD'; |
19831214 |
|
SET TimestampFormat='M/D/YY hh:mm:ss[.fff]'; |
12/14/83 13:15:30 |
|
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'; |
14/12/1983 13:15:30 |
|
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT'; |
14/12/1983 1:15:30 PM |
|
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff] TT'; |
1983-12-14 01:15:30 |
In the first load script SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT' is used. In the second load script the timestamp format is changed to SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]'. The different results show how the SET TimeFormat statement works with different time data formats.
The table below shows the data set that is used in the load scripts that follow. The second column of the table shows the format of each timestamp in the data set. The first five timestamps follow ISO 8601 rules but the sixth does not.
Data set
| transaction_timestamp | time data format |
|---|---|
| 2018-08-30 | YYYY-MM-DD |
| 20180830T193614.857 | YYYYMMDDhhmmss.sss |
| 20180830T193614.857+0200 | YYYYMMDDhhmmss.sss±hhmm |
| 2018-09-16T12:30-02:00 | YYYY-MM-DDhh:mm±hh:mm |
| 2018-09-16T13:15:30Z | YYYY-MM-DDhh:mmZ |
| 9/30/18 19:36:14 | M/D/YY hh:mm:ss |
In the Data load editor, create a new section, and then add the example script and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Load script
Results
| transaction_id | transaction_timestamp | LogTimeStamp |
|---|---|---|
| 3750 | 2018-08-30 | 2018-08-30 00:00:00 |
| 3751 | 20180830T193614.857 | 2018-08-30 19:36:14 |
| 3752 | 20180830T193614.857+0200 | 2018-08-30 17:36:14 |
| 3753 | 2018-09-16T12:30-02:00 | 2018-09-16 14:30:00 |
| 3754 | 2018-09-16T13:15:30Z | 2018-09-16 13:15:30 |
| 3755 | 9/30/18 19:36:14 | - |
The next load script uses the same data set. However, it uses SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]' to match the non-ISO 8601 format of the sixth timestamp.
In the Data load editor, replace the previous example script with the one below and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Load script
Results
| transaction_id | transaction_timestamp | LogTimeStamp |
|---|---|---|
| 3750 | 2018-08-30 | 2018-08-30 00:00:00 |
| 3751 | 20180830T193614.857 | 2018-08-30 19:36:14 |
| 3752 | 20180830T193614.857+0200 | 2018-08-30 17:36:14 |
| 3753 | 2018-09-16T12:30-02:00 | 2018-09-16 14:30:00 |
| 3754 | 2018-09-16T13:15:30Z | 2018-09-16 13:15:30 |
| 3755 | 9/30/18 19:36:14 | 2018-09-16 19:36:14 |
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!