Apache Pinot Docs
Search…
latest
DATETRUNC
This section contains reference documentation for the DATETRUNC function.
(Presto) SQL compatible date truncation, equivalent to the Presto function date_trunc.
Converts the value into a specified output granularity seconds since UTC epoch that is bucketed on a unit in a specified timezone.

Signature

DATETRUNC(unit, timeValue)
DATETRUNC(unit, timeValue, inputTimeUnitStr)
DATETRUNC(unit, timeValue, inputTimeUnitStr, timeZone)
DATETRUNC(unit, timeValue, inputTimeUnitStr, timeZone, outputTimeUnitStr)
unit supports the following values:
  • millisecond
  • second
  • minute
  • hour
  • day
  • week
  • month
  • quarter
  • year
inputTimeUnitStr and outputTimeUnitStr support the following values:
  • NANOSECONDS
  • MICROSECONDS
  • MILLISECONDS
  • SECONDS
  • MINUTES
  • HOURS
  • DAYS

Usage Examples

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight):
1
select dateTrunc('week', 1639480981746) AS ts
2
FROM ignoreMe
Copied!
or
1
select dateTrunc('week', 1639480981746, 'MILLISECONDS') AS ts
2
FROM ignoreMe
Copied!
ts
1639353600000
Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the UTC time zone, returning a result in epoch in seconds in UTC timezone:
1
select dateTrunc(
2
'week',
3
1639480981746,
4
'MILLISECONDS',
5
'UTC',
6
'SECONDS'
7
) AS ts
8
FROM ignoreMe
Copied!
ts
1639353600
Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the CET time zone, returning a result in epoch in seconds in UTC timezone:
1
select dateTrunc(
2
'week',
3
1639480981746,
4
'MILLISECONDS',
5
'CET',
6
'SECONDS'
7
) AS ts
8
FROM ignoreMe
Copied!
ts
1639350000
Truncates an epoch in milliseconds at QUARTER in the Los Angeles time zone (where a Quarter begins on Jan 1st, April 1st, July 1st, October 1st in Los Angeles timezone), returning a result in hours since UTC epoch:
1
select dateTrunc(
2
'quarter',
3
1639480981746,
4
'MILLISECONDS',
5
'America/Los_Angeles',
6
'HOURS'
7
) AS ts
8
FROM ignoreMe
Copied!
ts
453631
Last modified 1mo ago
Copy link