Date Functions

There also exist algebraic operators (+,  -) to add or subtract dates.

toDate(String dateTime, String format)

Creates a date time from a string and a format. Example: toDate("2019/03/28 14:06:42", "yyyy/MM/dd HH:mm:ss")

toDate(long milliseconds)

Creates a new date with Unix timestamp (milliseconds since January 1, 1970 UTC)

Year(<Date d>|<String s, Pattern p>)

Extracts the year part of the date

SELECT YEAR("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 2013

Month(<Date d>|<String s, Pattern p>)

Extracts the month part of the date

SELECT MONTH("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 12

Week(<Date d>|<String s, Pattern p>)

Returns the week number of the date

SELECT WEEK("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 52

Day(<Date d>|<String s, Pattern p>)

Extracts the day part of the date

SELECT DAY("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 24

Hour(<Date d>|<String s, Pattern p>)

Returns the hour of the date

SELECT HOUR("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 5

Minute(<Date d>|<String s, Pattern p>)

Returns the minute of the date

SELECT MINUTE("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 30

Second(<Date d>|<String s, Pattern p>)

Returns the second of the date

SELECT SECOND("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 10

Millisecond(<Date d>|<String s, Pattern p>)

Returns the millisecond of the date

SELECT MILLISECOND("12/24/13 5:30:10.970","M/d/y h:m:s.SSS") FROM Stream
=> 970

WeekDay(<Date d>|<String s, Pattern p>)

Returns the week day index of the date (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

SELECT WEEKDAY("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 3

DayOfMonth(<Date d>|<String s, Pattern p>)

Synonym for day().

SELECT DAYOFMONTH("12/24/13 5:30:10","M/d/y h:m:s") FROM Stream
=> 24

Years(Date d1, Date d2)

Computes the number of years between the two dates.

Months(Date d1, Date d2)

Computes the number of months between the two dates.

Days(Date d1, Date d2)

Computes the number of days between the two dates.

Hours(Date d1, Date d2)

Computes the number of hours between the two dates.

Minutes(Date d1, Date d2)

Computes the number of minutes between the two dates.

Seconds(Date d1, Date d2)

Computes the number of seconds between the two dates.

Milliseconds(Date d1, Date d2)

Computes the number of milliseconds between the two dates.

BusinessDays(Date from, Date to)

Computes the number of business days between the two dates.

CurDate()

Return the current system time specific date

DateInMillis()

Returns the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC

DateInMillis(Date d)

Returns the difference, measured in milliseconds, between the given date and midnight, January 1, 1970 UTC

toLong(Date d)

Same as DateInMillis(d)

NanoTime()

Returns the current value of the time source, in nanoseconds.

StreamDate()

Returns the current stream time specific date

ToString(Date d, Pattern p)

Converts the given date to a string value according to the given pattern.

format(Date d, String pattern)

Same as ToString, but evaluates Pattern only the first time (i.e. first pattern will be used for every data object)