Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Support for timezone offset from UTC indicated by +hh:mm, +hhmm, or +hh. For example, the date '2013-11-18 11:55-04:00' is recognized as a DateTime value.

  • Datetime part functions (for example, Hour) truncate time zones and return local time.
  • If you have a column with multiple time zones, you can convert the column to Unixtime so you can perform Date/Time operations with a standardized time zone. If you want to work with local times, you can truncate the time zone or use other Datetime functions. See UNIXTIME Function.

Two-digit year values

Depending on the system, a two-digit value for year in a Datetime value is subject to different interpretations. In 

D s product
, two-digit values for the year that are older than 80 years from the current year are forward-ported into the future. For example, in a job run on Dec 31, 2021, the date 01/01/41 is interpreted as 01/01/1941. However, if the job is run the next day (January 01, 2022), then the same data is interpreted as 01/01/2041.

Other systems use different limits for backward versus forward porting of year values:

As a result, it can be a challenge to manage these system-dependent two-digit years in a consistent manner. 

Tip

Tip: For best results, you should format year values as four-digit values before the data is ingested into

D s product
. Four-digit years are consistently represented across all systems.

If the above is not possible, you can create replacement steps in your recipe to convert two-digit years to four-digit values. In the following example, 00-39 is interpreted as a 19XX year, while 40-99 is interpreted as a 20XX year: 

D trans
RawWrangletrue
p03Value19$1
Typestep
WrangleText##NO_WRANGLE##
p01NameColumn
p01ValuemyDateColumn
p02NameFind
p02Value/\b([456789][0-9])\b$/
p03NameReplace with
SearchTermReplace text or pattern

and

D trans
RawWrangletrue
p03Value20$1
Typestep
WrangleText##NO_WRANGLE##
p01NameColumn
p01ValuemyDateColumn
p02NameFind
p02Value/\b([0123][0-9])\b$/
p03NameReplace with
SearchTermReplace text or pattern

Supported Datetime Formats

...