SHIFTTIMEZONE
Syntax
SHIFTTIMEZONE(<date>;<Time Zone 1>;<Time Zone 2>)
Description
This function allows you to assign a time zone to values in a date type column and then shift the values to a different time zone.
<Time Zone 1> is the time zone you want for the final values, <Time Zone 2> is the current time zone for the values being shifted. Time Zone 1 is usually the time zone of the Datameer X application server or the user's time zone (if different).
INFO
Information on time and date data within Datameer X pertaining to timezones:
- Datameer X internally stores all dates as universal time (UTC)
- Datameer X displays dates within workbooks as a local time for your Datameer X instance
- Datameer X recognizes the time zone for dates with an included parsed time zone
- Datameer X assigns your local instance time zone to dates parsed without a time zone
INFO
Time zones available for the function's arguments can be found at http://joda-time.sourceforge.net/timezones.html.
INFO
Datameer X always uses the following format for displaying dates:
Three character month abbreviation (space) two-digit day (comma)(space) four-digit year (space) two-digit hour (colon) two-digit minute (colon) two-digit second (space) two-character meridiem notation.
(e.g., 12.01.2012 gets transferred to Jan 12, 2012 12:00:00 AM)
When selecting a pattern, you select the way the data gets parsed (your input format, this can be connected to your systems representation of dates and times).
Use-case 1
Datameer X application server runs in the CET time zone (Berlin). A user located in Berlin, Germany has pulled in log files from a server located in Los Angeles, USA. The server logs all data using the local time zone but the dates extracted from the log files have no assigned time zone stamp. Because there is no time zone stamp, the user's machine interprets the log entries as happening in their local Berlin time. The SHIFTTIMEZONE function lets the user correct this by declaring the time zone they want for analysis "Europe/Berlin" (Time Zone 1) and the time zone that the data is currently in "America/Los_Angeles" (Time Zone 2).
| Formula builder | ||
|---|---|---|
| SHIFTTIMEZONE | ||
| Date | #Log_Time | Clarification (not available in Datameer X UI) |
| Time Zone 1 | Europe/Berlin | The time zone you want for final analysis |
| Time Zone 2 | America/Los_Angeles | The current time zone for values being shifted |
Log_Time Raw data (events occurred during Pacific time) | SHIFTTIMEZONE(#Log_Time;"Europe/Berlin";"America/Los_Angeles") Pacific timestamps represented in users time zone (Berlin) |
|---|---|
| Jul 15, 2019 02:00:00 AM | Jul 15, 2019 11:00:00 AM |
| Jul 15, 2019 03:00:00 AM | Jul 15, 2019 12:00:00 PM |
| Jul 15, 2019 04:00:00 AM | Jul 15, 2019 01:00:00 PM |
| Jul 15, 2019 05:00:00 AM | Jul 15, 2019 02:00:00 PM |
| Jul 15, 2019 06:00:00 AM | Jul 15, 2019 03:00:00 PM |
Use-case 2
A server registering events occurring in Los Angeles, USA is set to use UTC time zone and has not been configured to record any time zone in log entries. A user has pulled in log files from this server. The dates extracted from the log files have no time zone stamp assigned and represent UTC time. The SHIFTTIMEZONE function lets the user correct this by declaring the time zone they want for analysis "America/Los_Angeles " (Time Zone 1) and the time zone that the data is currently in "UTC" (Time Zone 2).
| Formula builder | ||
|---|---|---|
| SHIFTTIMEZONE | ||
| Date | #Log_Time | Clarification (not available in Datameer X UI) |
| Time Zone 1 | America/Los_Angeles | The timezone you want for final analysis |
| Time Zone 2 | UTC | The current timezone for values being shifted |
Log_Time Pacific timestamps represented in the server's time zone (UTC) | SHIFTTIMEZONE(#Log_Time;"America/Los_Angeles";"UTC") Real events timestamp according to the source local time zone |
|---|---|
| Jul 15, 2019 02:00:00 AM | Jul 14, 2019 07:00:00 PM |
| Jul 15, 2019 03:00:00 AM | Jul 14, 2019 08:00:00 PM |
| Jul 15, 2019 04:00:00 AM | Jul 14, 2019 09:00:00 PM |
| Jul 15, 2019 05:00:00 AM | Jul 14, 2019 10:00:00 PM |
| Jul 15, 2019 08:00:00 AM | Jul 15, 2019 01:00:00 AM |