DATETIMECONVERT

This section contains reference documentation for the DATETIMECONVERT function.

Converts the value from a column that contains a timestamp into another time unit and buckets based on the given time granularity.

Signature

DATETIMECONVERT(columnName, inputFormat, outputFormat, outputGranularity, bucketTimeZone)

inputFormat and outputFormat are defined using the following structure:

<time size>:<time unit>:<time format>:<pattern>

where:

• time size - size of the time unit eg: 1, 10

• time unit - DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS

• time format

  • EPOCH

  • SIMPLE_DATE_FORMAT pattern - defined in case of SIMPLE_DATE_FORMAT e.g. yyyy-MM-dd. A specific timezone can be passed using tz(timezone). Timezone can be long or short string format timezone. e.g. Asia/Kolkata or PDT

granularity is specified in the format <time size>:<time unit>.

bucketTimeZone - (optional) time zone used when bucketing, e.g. 'PST' or 'Europe/London' or '+00:00' . When parameter is set, bucketing is performed relative to time unit one level bigger than granularity unit (e.g. month for day granularity), even when input and output are epoch timestamps.

For example, when bucketing epoch millis of 2024-09-20T00:13:27.834Z to 5 hours we get:

• with no time zone -> 2024-09-19T20:00:00.000Z

• with explicit UTC time zone -> 2024-09-20T00:00:00.000Z

Similarly, when bucketing epoch millis of the date to 5 days we get:

• with no time zone -> 2024-02-19T00:00:00.000Z

• with explicit UTC time zone -> 2024-09-16T00:00:00.000Z

Usage Examples

These examples are based on the Batch JSON Quick Start.

created_at_timestamp from milliseconds since epoch to days since epoch, bucketed to 1 day granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS:EPOCH', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 11:00:00.0	1514804402000	17532

created_at_timestamp bucketed to 1 day granularity without time zone:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 12:00:02.0	1514804402000	1514764800000

created_at_timestamp bucketed to 1 day granularity using Europe/Berlin time zone:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS',
         'Europe/Berlin'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 12:00:02.0	1514804402000	1514761200000

created_at_timestamp bucketed to 15 minutes granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:EPOCH', 
         '15:MINUTES'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 11:00:00.0	1514804402000	1514804400000

created_at_timestamp to format yyyy-MM-dd, bucketed to 1 days granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 11:00:00.0	1514804402000	2018-01-01

created_at_timestamp to format yyyy-MM-dd HH:mm, in timezone Pacific/Kiritimati:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)', 
         '1:MILLISECONDS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 11:00:00.0	1514804402000	2018-01-02 01:00

created_at_timestamp to format yyyy-MM-dd, in timezone Pacific/Kiritimati and bucketed to 1 day granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 11:00:00.0	1514804402000	2018-01-02 00:00

created_at_timestamp to format yyyy-MM-dd, in UTC (default) timezone and bucketed to 1 day granularity :

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm', 
         '1:DAYS',
         'Europe/Berlin'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

id	created_at_timestamp	timeInMs	convertedTime
7044874134	2018-01-01 12:00:02.0	1514804402000	2017-12-31 23:00