Constructing date and time values

EQL provides functions to construct a timestamp representing time, date, or duration using an expression.

If the expression is a string, it must be in a certain format. If the format is invalid or the value is out of range, it results in NULL.

Function Description Format
TO_TIME Constructs a timestamp representing time.

<TimeStringFormat> ::= hh:mm:ss[.sss]((+|-) hh:mm |Z)

TO_DATETIME Constructs a timestamp representing date and time. See the section below for the syntax of this function's string interface, date-only numeric interface, and date-time numeric interface.
TO_DURATION Constructs a timestamp representing duration. <DurationStringFormat> ::=

[-]P[<Days>][T(<Hours>[<Minutes>}[<Seconds>]|

<Minutes>[<Seconds>]|

<Seconds>)]

<Days> ::= <Integer>D

<Hours> ::= <Integer>H

<Minutes> ::= <Integer>M

<Seconds> ::= <Integer>[.<Integer>]S

As stated in the Format column above, TO_TIME and TO_DATETIME accept time zone offset. However, EQL does not store the offset value. Instead, it stores the value normalized to the GMT time zone.

The following table shows the output of several date and time expressions:
Expression Normalized value
TO_DATETIME('2016-07-21T16:00:00.000+02:00') 2016-07-21T14:00:00.000Z
TO_DATETIME('2016-07-31T20:00:00.000-06:00') 2016-08-01T02:00:00.000Z
TO_DATETIME('2016-06-15T20:00:00.000Z') 2016-06-15T20:00:00.000Z
TO_TIME('23:00:00.000+03:00') 20:00:00.000Z
TO_TIME('15:00:00.000-10:00') 01:00:00.000Z

TO_DATETIME formats

The single-argument string interface for this function is: 
TO_DATETIME(<DateTimeString>)
where: 
<DateTimeString> ::= [-]YYYY-MM-DDT<TimeStringFormat>

Three examples of the string interface are listed in the table above.

The numeric interface signatures are: 
TO_DATETIME(<Year>, <Month>, <Day>)

TO_DATETIME(<Year>, <Month>, <Day>, <Hour>, <Minute>, <Second>, <Millisecond>)
where all arguments are integers.

In the first signature, time arguments will be filled with zeros. In both signatures, time zone will be assumed to be UTC. If time zone information exists, duration (TO_DURATION) and time zone (TO_TZ) constructs can be used, as shown below in the examples.

Examples of the numeric interface signatures are: 
TO_DATETIME(2016, 7, 22)

TO_DATETIME(2016, 7, 22, 23, 15, 50, 500)

TO_DATETIME(2016, 7, 22, 23, 15, 50, 500) + TO_DURATION(1000)

TO_TZ(TO_DATETIME(2016, 7, 22, 23, 15, 50, 500), 'America/New_York')