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`

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('2012-03-21T16:00:00.000+02:00')`	2012-03-21T14:00:00.000Z
`TO_DATETIME('2012-12-31T20:00:00.000-06:00')`	2013-01-01T02:00:00.000Z
`TO_DATETIME('2012-06-15T20:00:00.000Z')`	2012-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(2012, 9, 22)

TO_DATETIME(2012, 9, 22, 23, 15, 50, 500)

TO_DATETIME(2012, 9, 22, 23, 15, 50, 500) + TO_DURATION(1000)

TO_TZ(TO_DATETIME(2012, 9, 22, 23, 15, 50, 500), 'America/New_York')