TIME_FROM_FORMAT

The TIME_FROM_FORMAT function takes a string representation of a time and converts it into a time data type.

This function requires a single string, representing a time, as input. By default, the TIME_FROM_FORMAT function will process this string as though it is in the ISO 8601 Format ("YYYY-MM-DDThh:mm:ssZ") but another optional input string can be given to define the format differently. This function then outputs the given time as a Time data type.

Declaration

TIME_FROM_FORMAT(time_string, format_options) -> time

Parameters

time_string (required, type: string)

A string representing a time.

format_options (optional, type: **string, **default: ISO 8601 Format: "YYYY-MM-DDThh:mm:ssZ")

A string defining the format of the time to be converted.

Return Values

time (type: time)

The time described in time_string, as a Time data type.

Examples

The following example takes the string "2007-01-09T09:41:00" and converts the time represented by that string into the time data type. Note that the values associated with the returned hour and minute match the hour and minute given by the input string:

TIME_FROM_FORMAT("2007-01-09T09:41:00") -> {  
 "hour": 9,  
 "minute": 41,  
 "second": 0,  
 "millisecond": 0  
}

The time described in ISO 8601 Format in the above example is in simple UTC, but this does not have to be the case. TIME_FROM_FORMAT will return a time in whatever timezone is given. The follow example describes a time with a timezone offset of +8 hours. Note how this is taken into account by returning an hour value eight hours ahead of the the hour value given:

TIME_FROM_FORMAT("2007-01-09T09:41:00-08:00") -> {  
 "hour": 17,  
 "minute": 41,  
 "second": 0,  
 "millisecond": 0  
}

The ISO 8601 Format provides a lot of information that is not necessary for returning a valid time data type. However, while in this regard the year, month, and day are extraneous, they cannot be removed without deviating from ISO 8601 Format. The following example attempts to take "T09:41:00" and convert it into a time data type, but because the input string is not in ISO 8601 Format, TIME_FROM_FORMAT cannot interpret it without more information on how the input string ought to be parsed:

TIME_FROM_FORMAT("T09:41:00") -> NULL

In order to designate how the string "T09:41:00" can be properly interpreted as a time_string, a value must be given for format_options. The string "T09:41:00" designates the time in the format "Thh:mm:ss" ("T", followed by two digits designating the hour, a colon, two digits designating the minutes, another colon, and two digits designating the seconds), and when format_options declares this, as in the following example, TIME_FROM_FORMAT can interpret the given time_string as a time just like it did for the first example:

TIME_FROM_FORMAT("T09:41:00", "Thh:mm:ss") -> {  
 "hour": 9,  
 "minute": 41,  
 "second": 0,  
 "millisecond": 0  
}

As long as the format is designated by format_options in accordance with the conventions discussed in this table, TIME_FROM_FORMAT can parse a myriad of differently formatted dates and times. The following example takes the string "9:41 am", designates that it should be parsed in the format "h:mm a" (that is, as many digits as are required to designate the hour, a colon, two digits to designate the minute, a space, and then a lowercase designation of AM or PM), and then outputs the time date type indicating 9:41 AM:

TIME_FROM_FORMAT("9:41 am", "h:mm a") -> {  
 "hour": 9,  
 "minute": 41,  
 "second": 0,  
 "millisecond": 0  
}

Discussion

The examples given above regarding how time_string may be formatted are far from comprehensive, as any and all combinations of the tokens given in this table can be used to describe valid ways of parsing strings that indicate particular times. For further discussion and examples of how these tokens can be applied, check out FORMAT_TIME and TIME_FROM_DATETIME.