This section contains reference documentation for the DATETIMECONVERT function.
Converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity.
DATETIMECONVERT(columnName, inputFormat, outputFormat, outputGranularity)
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>
.
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:
7044874134
2018-01-01 11:00:00.0
1514804402000
17532
created_at_timestamp
bucketed to 15 minutes granularity:
7044874134
2018-01-01 11:00:00.0
1514804402000
1514804400000
created_at_timestamp
to format yyyy-MM-dd
, bucketed to 1 days granularity:
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
:
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:
7044874134
2018-01-01 11:00:00.0
1514804402000
2018-01-02 00:00