String to Date&Time

Parses strings in selected columns and converts them to date&time cells. You can choose from common date&time formats or specify a custom format. Locales can be set to handle localized month or weekday names.
Useful when converting imported date strings into date&time format for analysis and processing.

Options

String columns
The string columns to convert to date&time columns.
Locale
A locale can be chosen, which determines the language and geographic region for terms such as months or weekdays.
Input format
A format string (defined by DateTimeFormatter).
Examples:
  • "yyyy.MM.dd HH:mm:ss.SSS" produces dates such as "2001.07.04 12:08:56.000"
  • "yyyy-MM-dd'T'HH:mm:ss.SSSZ" produces dates such as "2001-07-04T12:08:56.235-0700"
  • "yyyy-MM-dd'T'HH:mm:ss.SSSXXX'['VV']'" produces dates such as "2001-07-04T12:08:56.235+02:00[Europe/Berlin]"
Supported placeholders in the pattern are:
  • G: era
  • u: year
  • y: year of era
  • D: day of year
  • M: month in year (context sensitive)
  • L: month in year (standalone form)
  • d: day of month
  • Q/q: quarter of year
  • Y: week based year (you probably want to use y instead)
  • w: week of week based year
  • W: week of month
  • E: day of week
  • e: localized day of week
    • e: 4
    • ee: 04
    • eee: Wed
    • eeee: Wednesday
    • eeeee: W
  • c: day of week
  • F: day-of-week in month
  • a: am/pm of day
  • h: clock hour of am/pm (1-12)
  • K: hour of am/pm (0-11)
  • k: clock hour of am/pm (1-24)
  • H: hour of day (0-23)
  • m: minute of hour
  • s: second of minute
  • S: fraction of second
  • A: milli of day
  • n: nano of second
  • N: nano of day
  • V: time zone ID
  • z: time zone name
  • O: localized zone offset
  • x: zone offset (ISO8601)
    • X: +08 or +0830
    • XX: +0800 or +0830 (no colons)
    • XXX: +08:00 or +08:30 (with colons)
    • XXXX: +0800 or +083015 (i.e. including offset seconds, no colons)
    • XXXXX: +08:00 or +08:30:15 (i.e. including offset seconds, with colons)
  • X: same as x, but outputs Z when offset is 0
  • Z: zone offset (RFC822)
    • Z, ZZ, ZZZ: +0800 or +0830
    • ZZZZ: GMT+08:00 or GMT+08:30
    • ZZZZZ: +08:00 or +08:30:15
  • p: pad next
  • ' : escape for text
  • '': single quote
  • [: optional section start
  • ]: optional section end
Auto-guess format

Try to guess the format of the selected column and set the format accordingly.

This is done by checking the first 1000 non-missing rows of the first selected column and trying to find a suitable format that can parse every entry. If no suitable format is found, the format will be set to an empty string.

If extraction fails
If set to 'Fail', the node will abort the execution and fail on errors. Otherwise, missing values will be generated instead.
  • Insert missing: Sets the output cell to missing if the string column cannot be converted to the specified type.
  • Fail: Fails with an error if the string column cannot be converted to the specified type.
Output columns
Depending on this setting, the output columns will either replace the modified columns, or be appended to the table with a suffix.
  • Replace: Replaces the selected columns by the new columns.
  • Append with suffix: Appends the selected columns to the input table with a new name that is the previous name plus the provided suffix.
Output column suffix
The suffix to append to the column names of the new columns.

Input Ports

Icon
Input table containing string columns with date&time information.

Output Ports

Icon
Output table containing the parsed columns.

Popular Predecessors

  • No recommendations found

Popular Successors

  • No recommendations found

Views

This node has no views

Workflows

Links

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.