Template Strings to Create Specific Output Timestamps - Amazon Kinesis Data Analytics SQL Reference

Template Strings to Create Specific Output Timestamps

Template Input String Output TIMESTAMP Notes
'yyyy-MM-dd hh:mm:ss' '2009-09-16 03:15:24' '2009-09-16 03:15:24'
'yyyy-mm' '2011-02-08 07:23:19' '2011-01-01 00:02:00'

The template above specifies only year first and minutes second, so the second element in the input string ("02") is used as minutes. Default values are used for Month and Day ("01") and for hours and seconds ("00").
'MMM dd, yyyy' 'March 7, 2010' '2010-03-07 00:00:00'

MMM in the template above matches "March"; the template's 'comma space' matches the input string.

If the template lacks the comma, so must the input string, or there is no output;

If the input string lacks the comma, so must the template.
'MMM dd,' 'March 7, 2010' '1970-03-07 00:00:00'

Note that the template above doesn't use a year specifier, causing the output TIMESTAMP to use the earliest year in this epoch, 1970.
'MMM dd,y' 'March 7, 2010' '2010-03-07 00:00:00'

Using the template above, if the input string were 'March 7, 10', the output TIMESTAMP would be '0010-03-07 00:00:00'.
'M-d' '2-8' '1970-02-08 00:00:00'

Absent a yyyy specifier in the template, as above, the earliest year in this epoch (1970) is used.

An input string of '2–8−2011' would give the same result; using '2011–2−8' would give no result because 2011 is not a valid month.
'MM-dd-yyyy' '06-23-2011 10:11:12' '2011-06-23 00:00:00'

Dashes as delimiters (as above) are fine, if template and input both use them in the same positions. Since the template omits hours, minutes, and seconds, zeroes are used in the output TIMESTAMP.

'dd-MM-yy hh:mm:ss'

'23-06-11 10:11:12'

'2011-06-23 10:11:12'

You can have the specifiers in any order as long as that order matches the meaning of the input string you supply. The template and input string of the next example below have the same meaning (and the same output TIMESTAMP) as this example, but they specify months before days and seconds before hours.

'MM-dd-yy ss:hh:mm'

'06-23-11 12:10:11'

'2011-06-23 10:11:12'

In the template used above, the order of the month and day specifiers is reversed from the example just above, and the specifier for seconds is before hours instead of after minutes; but because the input string also puts months before days and seconds before hours, the meaning (and the output TIMESTAMP) is the same as the example ABOVE.

'yy-dd-MM ss:hh:mm'

'06-23-11 12:10:11'

'2006-11-23 10:11:12'

The template used above reverses (compared to the prior example above) the years and months specifiers, while the input string remains the same. In this case, the output TIMESTAMP uses the first element of the input string as the years, the second as the days, and the third as the months.

'dd-MM-yy hh:mm'

'23-06-11 10:11:12'

'2011-06-23 10:11:00'

With seconds omitted in the template, as above, the output TIMESTAMP uses 00 seconds. Any number of y specifiers produces the same result; but if the input string inadvertently uses a 1 instead of 11 for the year, as in '23-06-1 10:11:12', then the output TIMESTAMP becomes '0001-06-23 10:11:00'.

'MM/dd/yy hh:mm:ss'

'12/19/11 10:11:12'

'12/19/11 12:11:12'

'2011-12-19 10:11:12'

'12/19/11 00:11:12'

Slashes as delimiters are fine, if template and input both use them in the same positions, as above. Using specifier hh, input times of 12:11:10 and 00:11:10 have the same meaning as a time in the morning.

'MM/dd/yy HH:mm:ss'

'12/19/11 12:59:59'

'12/19/11 21:08:07'

'2011-12-19 00:11:12'

'2011-12-19 12:11:12'

'2011-12-19 12:59:59'

'2011-12-19 21:08:07'

The input-string values '2011-12-19 00:11:12' or '2011-12-19 12:11:12' would fail with this template because '2011' is not a month, as required/expected by the template-string 'MM/dd/yy HH:mm:ss'.

However, changing the template gives useful output:

values(cast(CHAR_TO_TIMESTAMP('y/MM/dd HH:mm:ss', '2011/12/19 00:11:12') as
varchar(19)));
'EXPR$0'
'2011-12-19 00:11:12'

1 row selected

'12/19/11 00:11:12' would fail with the above template ('y/MM/dd'), since 19 is not a valid month; supplying '12/11/19 00:11:12' works.

'2011-12-19 12:11:12' would fail as input because dashes don't match the slashes in the template, '2011/12/19 12:11:12' works.

Note that for times after 12 noon (that is, for afternoon and evening times), the hours specifier must be HH instead of hh, and the input string must specify the afternoon or evening hour in 24-hour clock time, hours running from 00 to 23.

Using specifier HH, input times of 12:11:10 and 00:11:10 have different meanings, the first as a time in the afternoon and the second as a time in the morning.

Using the specifier hh, the times from 12:00 through 11:59:59 are morning times:

  • Given the specifiers hh:mm:ss, the output TIMESTAMP will include '00:09:08' in the morning for both input string '12:09:08' and input string '00:09:08'; whereas,

  • Given the specifiers HH:mm:ss, the output TIMESTAMP for input string '00:09:08' in the morning will include '00:09:08'

    and the output TIMESTAMP for input string '12:09:08' in the afternoon will include '12:09:08'.