1 of 30

Date & Time

This page describes how to use date and time functions in Lenses SQL Processors.

Every Date Math expression starts with a base date or time followed by the addition or subtraction of one or more durations.

The base date or time (from here onward) is derived from a field in a table or a function such as now() or yesterday() that generates datetime values.

The shorthand syntax is a unit value followed by a unit symbol. The symbols are:

y (year)
M (month)
w (week)
d (day)
h (hour)
m (minute)
s (second)

CONVERT_DATETIME

This page describes the CONVERT_DATETIME function in Lenses SQL.

CONVERT_DATETIME (strExpr, fromPattern, toPattern)

Converts the string format of a date [and time] to another using the pattern provided.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
 CONVERT_DATETIME(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss', 'dd-MM-yyyy HH:mm'), 
 tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "CONVERT_DATETIME": "01-01-2016 00:00",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

DATE

This page describes the DATE function in Lenses SQL.

DATE(days)

Builds a local date value from a long or int value. This function can also be used with no parameters to return the current ISO date.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT DATE() 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "DATE": "2023-11-29T00:00:00Z"
  }
}

DATETIME

This page describes the DATETIME function in Lenses SQL.

Provides the current ISO date and time.

Available in:

Processors

SQL Studio

Sample code:

Output:

EXTRACT_TIME

This page describes the EXTRACT_TIME function in Lenses SQL.

EXTRACT_TIME(timestamp)

Extracts the time portion of a timestamp-micros or timestamp-millis returning a time-millis or time-micros value depending on the timestamp precision.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    EXTRACT_TIME(_meta.timestamp), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "EXTRACT_TIME": "20:28:45.41",
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

EXTRACT_DATE

This page describes the EXTRACT_DATE function in Lenses SQL.

EXTRACT_DATE(timestamp)

Extracts the date portion of a timestamp-micros or timestamp-millis returning a date value.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    EXTRACT_DATE(_meta.timestamp), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "EXTRACT_DATE": "2023-11-28",
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

FORMAT_DATE

This page describes the FORMAT_DATE function in Lenses SQL.

Returns a string representation of a date value according to a given pattern.

Available in:

Processors

SQL Studio

Sample code:

Output:

FORMAT_TIME

This page describes the FORMAT_TIME function in Lenses SQL.

Returns a string representation of a time value according to a given pattern.

Available in:

Processors

SQL Studio

Sample code:

Output:

FORMAT_TIMESTAMP

This page describes the FORMAT_TIMESTAMP function in Lenses SQL.

FORMAT_TIMESTAMP(timestamp,output_pattern)

Returns a string representation of a timestamp value according to a given pattern.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    FORMAT_TIMESTAMP(_meta.timestamp,'yyyy-MM-dd HH:mm:ss'), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "FORMAT_TIMESTAMP": "2023-11-28 20:28:45",
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

HOUR

This page describes the HOUR function in Lenses SQL.

HOUR (expr)

Extracts the hour component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    HOUR(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "HOUR": 20,
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

MONTH_TEXT

This page describes the MONTH_TEXT function in Lenses SQL.

MONTH_TEXT(dataExpr)

Returns the month name.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    MONTH_TEXT(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "MONTH_TEXT": "November",
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

MINUTE

This page describes the MINUTE function in Lenses SQL.

MINUTE(dataExpr)

Extracts the minute component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    MINUTE(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "MINUTE": 28,
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

MONTH

This page describes the MONTH function in Lenses SQL.

MONTH(dataExpr)

Builds a timestamp-millis value from a long or int value.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    MONTH(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "MONTH": 11,
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

PARSE_DATE

This page describes the PARSE_DATE function in Lenses SQL.

Builds a date value given a date string representation and a date pattern.

Available in:

Processors

SQL Studio

Sample code:

Output:

PARSE_TIME_MILLIS

This page describes the PARSE_TIME_MILLIS function in Lenses SQL.

PARSE_TIME_MILLIS(millis, pattern)

Builds a time-millis value given a time string representation and a time pattern.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    PARSE_TIME_MILLIS(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "PARSE_TIME_MILLIS": "00:00:02",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

PARSE_TIME_MICROS

This page describes the PARSE_TIME_MICROS function in Lenses SQL.

Builds a time-micros value given a time string representation and a time pattern.

Available in:

Processors

SQL Studio

Sample code:

Output:

PARSE_TIMESTAMP_MILLIS

This page describes the PARSE_TIMESTAMP_MILLIS function in Lenses SQL.

Builds a timestamp-millis value given a datetime string representation and a date time pattern.

Available in:

Processors

SQL Studio

Sample code:

Output:

PARSE_TIMESTAMP_MICROS

This page describes the PARSE_TIMESTAMP_MICROS function in Lenses SQL.

Builds a timestamp-micros value given a datetime string representation and a date time pattern.

Available in:

Processors

SQL Studio

Sample code:

Output:

SECOND

This page describes the SECOND function in Lenses SQL.

SECOND(dataExpr)

Extracts the second component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    SECOND(_meta.timestamp), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "SECOND": 45,
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

TIMESTAMP

This page describes the TIMESTAMP function in Lenses SQL.

Returns a timestamp for a given date and time at a specific zone id.

Available in:

Processors

SQL Studio

Sample code:

Output:

TIME_MICROS

This page describes the TIME_MICROS function in Lenses SQL.

Builds a time-micros value from a long or int value.

Available in:

Processors

SQL Studio

Sample code:

Output:

TIMESTAMP_MICROS

This page describes the TIMESTAMP_MICROS function in Lenses SQL.

Builds a timestamp-micros value from a long or int value.

Available in:

Processors

SQL Studio

Sample code:

Output:

TIME_MILLIS

This page describes the TIME_MILLIS function in Lenses SQL.

TIME_MILLIS(millis)

Builds a time-millis value from a long or int value.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    TIME_MILLIS(Timestamp), 
    Timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "TIME_MILLIS": "03:07:59.39",
    "Timestamp": "1503158676433679390"
  }
}

TIMESTAMP_MILLIS

This page describes the TIMESTAMP_MILLIS function in Lenses SQL.

TIMESTAMP_MILLIS(millis)

Builds a timestamp-millis value from a long or int value.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    TIMESTAMP_MILLIS(Timestamp), 
    Timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "TIMESTAMP_MILLIS": "+47635172-03-26T03:07:59.39Z",
    "Timestamp": "1503158676433679390"
  }
}

TO_DATE

This page describes the TO_DATE function in Lenses SQL.

TO_DATE(strExpr, pattern)

Converts a string representation of a date into epoch value using the pattern provided.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    TO_DATE(tpep_dropoff_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_dropoff_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "TO_DATE": "2016-01-01T00:00:00Z",
    "tpep_dropoff_datetime": "2016-01-01 00:11:14"
  }
}

TO_DATETIME

This page describes the TO_DATETIME function in Lenses SQL.

TO_DATETIME(strExpr, pattern)

Converts a string representation of a datetime into epoch value using the pattern provided.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    TO_DATETIME(tpep_dropoff_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_dropoff_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "TO_DATETIME": "2016-01-01T00:11:14Z",
    "tpep_dropoff_datetime": "2016-01-01 00:11:14"
  }
}

TOMORROW

This page describes the TOMORROW function in Lenses SQL.

Returns the current date time plus 1 day.

Available in:

Processors

SQL Studio

Sample code:

Output:

TO_TIMESTAMP

This page describes the TO_TIMESTAMP function in Lenses SQL.

Converts a string representation of a date into epoch value using the pattern provided.

Converts a string using a pattern to a date and time type.

Available in:

Processors

SQL Studio

Sample code:

Output:

YEAR

This page describes the YEAR function in Lenses SQL.

Extracts the year component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

Sample code:

Output:

YESTERDAY

This page describes the YESTERDAY function in Lenses SQL.

YESTERDAY()

Returns the current date time minus 1 day.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT YESTERDAY() 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "YESTERDAY": "2023-11-28T18:57:13.27Z"
  }
}