TO_TIME

The TO_TIME function parses a datetime string according to a formatting specification, and returns a  TIME value. Note that the input string can represent a timestamp or time; however, the input string must match the formatting specification string.

Syntax

TO_TIME( timeStrExpr, formatStr );

timeStrExpr

A string expression that contains a time that is formatted according to the format string.

formatStr

A string that specifies the format you want applied to the timeStr. See the Date and Time Formats section below for more information about format specification.

Results

The result is always a  TIME value.

Date and Time Formats

Splice Machine supports date and time format specifications based on the Java DateTimeFormatter class.

Date and time value formats are used for both parsing input values and pattern types. For example, the format specification yyyy-MM-dd HH:mm:ssZ parses values like 2014-03-02 11:47:44-0800.

The remainder of this topic describes format specifications in these sections:

  • Pattern Specifications contains a table showing details for all of the pattern letters you can use.
  • Pattern Types describes how certain pattern letters are interpreted for parsing and/or formatting.
  • Examples contains a number of examples that will help you understand how to use formats.

Pattern Specifications

You can specify formatting or parsing patterns for date-time values using the pattern letters shown in the following table. Note that pattern letters are typically repeated in a format specification. For example, yyyy or yy. Refer to the next section for specific information about multiple pattern letters in the different pattern types.

Pattern Letter Meaning Pattern Type Example(s)
G Era Text AD; Anno Domini; A
u Year Year 2017; 17
y Year-of-era Year 2017; 17
D Day-of-year Number 189; 303
M/L Month-of-year Number/text 6; 06; Jun; June
d Day-of-month Number 12
Y Week-based-year year 2017; 17
w Week-of-week-based-year Number 14; 51
W Week-of-month Number 7
E Day-of-week Text Mon; Monday; M
e/c Localized day-of-week Number/text 1; 01; Mon; Monday; M
F Week-of-month Number 2
a Am-Pm-of-Day Text PM
h Clock-hour-of-am-pm (1-12) Number 7; 12
K Hour-of-am-pm (0-11) Number 0; 11
k Clock-hour-of-am-pm (1-24) Number 1; 13
H Hour-of-day (0-23) Number 0; 11; 17
m Minute-of-hour Number 27
s Second-of-minute Number 48
S Fraction-of-second Fraction 978
A Milli-of-day Number 1234
V Time-zone ID Zone-id America/San_Francisco; Z; -08:30
z Time-zone name Zone-name Pacific Standard Time; PST
O Localized zone-offset Offset-0 GMT+7; GMT+07:00; UTC-07:00;
X Zone-offset 'Z' for zero Offset-X Z; -07; -0730; -07:30; -073015; -07:30:15;
x Zone-offset Offset-x +0000; -07; -0730; -07:30; -073015; -07:30:15;
Z Zone-offset Offset-Z +0000; -0800; -08:00;
p Pad next Pad modifier 1
' Escape for text Delimiter  
'' Single quote Literal '

The format characters x, X, and Z all allow the matching of any possible timezone offest, and all cause the same behavior.

Pattern Types

How a pattern type is interpreted for certain pattern letters depends on the number of repeated letters in the pattern. In some cases, as noted in the following table, other factors can influence how the pattern is interpreted.

Pattern Type Description
Text

The text style is determined based on the number of pattern letters used;

  • Less than 4 pattern letters will use the short form.
  • Exactly 4 pattern letters will use the full form.
  • Exactly 5 pattern letters will use the narrow form. Pattern letters L, c, and q specify the stand-alone form of the text styles.
Number

If the count of letters is one, then the value is parsed using the minimum number of digits and without padding. Otherwise, the count of digits is used as the width of the field, with the value zero-padded as necessary.

The following pattern letters have constraints on the count of letters:

  • Only one letter of c and F can be specified.
  • Up to two letters of d, H, h, K, k, m, and s can be specified.
  • Up to three letters of D can be specified.
Number/Text If the count of pattern letters is 3 or greater, use the Text rules above. Otherwise use the Number rules above.
Fraction Specifying any number of fractional seconds ('S') will accept between 1 and 6 fractional seconds digits.
Year

The count of letters determines the minimum field width below which padding is used.

  • If the count of letters is two, then a reduced two digit form is used. This will parse using the base value of 2000 to 2099 inclusive.
  • If the count of letters is less than four (but not two), then the sign is only parsed for negative years. Otherwise, the sign is parse if the pad width is exceeded.
ZoneId This specifies the time-zone ID, such as Europe/Paris. If the count of letters is two, then the time-zone ID is used. Any other count of letters results in a syntax error.
Zone names This specifies the display name of the time-zone ID. If the count of letters is one, two or three, then the short name is used. If the count of letters is four, then the full name is used. Five or more letters results in a syntax error.
Offset O

This formats the localized offset based on the number of pattern letters.

  • One letter specifies the short form of the localized offset, which is localized offset text, such as GMT, with hour without leading zero, optional 2-digit minute and second if non-zero, and colon, for example GMT+8.
  • Four letters specifies the full form, which is localized offset text, such as GMT, with 2-digit hour and minute field, optional second field if non-zero, and colon, for example GMT+08:00.
  • Any other count of letters results in a syntax error.
Offset Z

This formats the offset based on the number of pattern letters.

  • One, two or three letters specifies the hour and minute, without a colon, such as +0130. This will be +0000 when the offset is zero.
  • Four letters specifies the full form of localized offset, equivalent to four letters of Offset-O. This will be the corresponding localized offset text if the offset is zero.
  • Five letters specifies the hour, minute, with optional second if non-zero, with colon. It specifies Z if the offset is zero.
  • Six or more letters results in a syntax error.
Optional section The optional section markers work exactly like calling DateTimeFormatterBuilder.optionalStart() and DateTimeFormatterBuilder.optionalEnd().
Pad modifier

Modifies the pattern that immediately follows to be padded with spaces. The pad width is determined by the number of pattern letters. This is the same as calling DateTimeFormatterBuilder.padNext(int).

For example, ppH specifies the hour-of-day padded on the left with spaces to a width of 2.

 

Any unrecognized letter is an error.

Any non-letter character, other than [, ], {, }, # and the single quote is parsed. Despite this, it is recommended to use single quotes around all characters that you want to parse directly to ensure that future changes do not break your application.

Formatting Examples

The following table contains a number of examples of date time formats:

Date and Time Pattern Example
"yyyy.MM.dd G 'at' HH:mm:ss z" 2001.07.04 AD at 12:08:56 PDT
"EEE, MMM d, ''yy" Wed, Jul 4, '01
"h:mm a" 12:08 PM
"hh 'o''clock' a, zzzz" 12 o'clock PM, Pacific Daylight Time
"K:mm a, z" 0:08 PM, PDT
"yyyyy.MMMMM.dd GGG hh:mm aaa" 02001.July.04 AD 12:08 PM
"EEE, d MMM yyyy HH:mm:ss Z" Wed, 4 Jul 2001 12:08:56 -0700
"yyMMddHHmmssZ" 010704120856-0700
"yyyy-MM-dd'T'HH:mm:ss.SSSZ" 2001-07-04T12:08:56.235-0700
"yyyy-MM-dd'T'HH:mm:ss.SSSXXX" 2001-07-04T12:08:56.235-07:00
"YYYY-'W'ww-e" 2001-W27-3

Usage Notes

You can supply a string formatted as a timestamp to TO_TIME; it will translate that value into a time. Note, however, that it cannot translate a date string into a date value.

Note that our examples use lowercase year (e.g. yyyy) and day (e.g. dd) formats; not uppercase (e.g. YYYY or DD) formats. It’s easy to get confused, so remember:

The uppercase YYYY format, which is not commonly used, specifies week year, which means that you must also specify a week-of-week-based-year (w) format value.

Similarly, the uppercase D format, also uncommon, specifies day-of-the-year, not the day of the month.

Examples of Using TO_TIME

Here’s an example:

splice> VALUES TO_TIME('11:44', 'HH:mm');
1
----------
11:44:00

The following example that shows two interesting aspects of using TO_TIME. In this example, the input includes the literal T ), which means that the format pattern must delimit that letter with single quotes. Since we’re delimiting the entire pattern in single quotes, we then have to escape those marks and specify ''T'' in our parsing pattern.

Note that when you specify a zone offset (Z) or time zone (z), Splice Machine interprets the timestamp in its given zone, and then adjusts the time to the time zone setting of the operating system. This means that when a timestamp value specified in the Easter time zone is parsed on an operating system based in the Pacific time zone, it will be adjusted back by three hours; for example:

splice> VALUES TO_TIME('2013-06-18T01:03:30.000EDT','yyyy-MM-dd''T''HH:mm:ss.SSSz');
1
-----------
22:03:30

See Also