String Date Conversion

Section 1 - date and time SQL tokens

description

Databases usually provide a function to convert string values to dates/timestamps, such as “TO_TIMESTAMP(string, format)”. There are different types of format codes. The most common one is assembled based on the datetime parts. This section configures how Insights should generate the format parameter by assembling various parts.

available tokens

1. YY : 2 digit year
2. YYYY : 4 digit year
3. MM: month
4. DD: day
5. hh: hours (0-12)
6. HH: hours (0-24)
7. mm: minutes
8. ss: seconds
9. ms: fractional seconds
10. mon: month names (in English)
11. ampm: AM or PM

default settings

date and time sql tokens:
  YY: yy
  YYYY: yyyy
  MM: MM
  DD: dd
  hh: hh
  HH: HH
  mm: mi
  ss: ss
  ms: ms
  mon: mon
  ampm: am

Based on the settings above, if the input string value is “1999-01-01 02:00:00.001 AM”, Insights will generate a format of “yyyy-MM-dd hh:mm:ss.ms am”.

Section 2 - date format codes

description

For databases that uses other types of format codes, you may configure them in this section. You may also use this section if you always only work with a small number of datetime formats. Configuring them directly in this section will allow Insights to use the configured format directly instead of assembling them.

Note that “date format codes” section takes priority over “date and time sql tokens” section. It means if Insights finds a match in the date format codes list, it will not attempt to assemble a format by itself by using the tokens.

example

Here is a list of most common datetime formats (left), and you just need to update the values (right) for each based on your database syntaxes.

date format codes:
  # US
  "mm/dd/yy": mm/dd/yy
  "mm/dd/yyyy": mm/dd/yyyy
  # ANSI
  "yy.mm.dd": yy.mm.dd
  "yyyy.mm.dd": yyyy.mm.dd
  # British/French
  "dd/mm/yy": dd/mm/yy
  "dd/mm/yyyy": dd/mm/yyyy
  # German
  "dd.mm.yy": dd.mm.yy
  "dd.mm.yyyy": dd.mm.yyyy
  # Italian
  "dd-mm-yy": dd-mm-yy
  "dd-mm-yyyy": dd-mm-yyyy
  # Japan
  "yy/mm/dd": yy/mm/dd
  "yyyy/mm/dd": yyyy/mm/dd
  # ISO
  "yymmdd": yymmdd
  "yyyymmdd": yyyymmdd
  # month name
  "dd mon yy": dd mon yy
  "dd mon yyyy": dd mon yyyy
  "Mon dd, yy": mon dd, yy
  "Mon dd, yyyy": mon dd, yyyy