GBase 8a date_trunc: Truncating Date/Time to a Specified Precision

The date_trunc function in GBase 8a truncates a date/time value to the specified precision. Everything below that precision is set to its minimal value (e.g., truncating to month resets the day, hour, minute, and second to the first day of the month at 00:00:00). This is extremely useful for time‑series aggregation and data alignment in a gbase database.

Syntax

date_trunc(type, date)

• type: the precision to truncate to, such as year, quarter, month, week, day, hour, minute, second, century, decade, millennium, milliseconds, microseconds. Both quoted and unquoted forms are accepted.
• date: a datetime expression (usually DATETIME).

Common Truncation Types

type	Truncation Effect	Example Output for '2023-04-21 11:12:13.56789'
year	Resets to Jan 1 00:00:00	2023-01-01 00:00:00
quarter	First day of the quarter 00:00:00	2023-04-01 00:00:00
month	First day of the month 00:00:00	2023-04-01 00:00:00
week	Monday of the week 00:00:00	2023-04-17 00:00:00
day	Time set to 00:00:00	2023-04-21 00:00:00
hour	Minutes and seconds set to zero	2023-04-21 11:00:00
minute	Seconds set to zero	2023-04-21 11:12:00
second	Sub‑seconds set to zero	2023-04-21 11:12:13
century	First day of the century	2001-01-01 00:00:00
decade	First day of the decade	2020-01-01 00:00:00
millennium	First day of the millennium	2001-01-01 00:00:00
milliseconds	Microseconds set to zero	2023-04-21 11:12:13.567000
microseconds	Sub‑microseconds set to zero	2023-04-21 11:12:13.567890

Note: week respects the default_week_format parameter; a date at the very beginning of a year may belong to the last week of the previous year.

Examples

-- Truncate to year
SELECT date_trunc(year, '2023-04-21 11:12:13.56789');
-- Result: 2023-01-01 00:00:00

-- Truncate to quarter
SELECT date_trunc(quarter, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-01 00:00:00

-- Truncate to month
SELECT date_trunc(month, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-01 00:00:00

-- Truncate to week (Monday of the week)
SELECT date_trunc(week, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-17 00:00:00

-- A date at the start of the year may fall into the previous year's week
SELECT date_trunc(week, '2023-01-01 11:12:13.56789');
-- Result: 2022-12-26 00:00:00

-- Truncate to day
SELECT date_trunc(day, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 00:00:00

-- Truncate to hour
SELECT date_trunc(hour, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:00:00

-- Truncate to minute
SELECT date_trunc(minute, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:00

-- Truncate to second
SELECT date_trunc(second, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:13

-- Truncate to century
SELECT date_trunc(century, '2023-04-21 11:12:13.56789');
-- Result: 2001-01-01 00:00:00

-- Truncate to decade
SELECT date_trunc(decade, '2023-04-21 11:12:13.56789');
-- Result: 2020-01-01 00:00:00

-- Truncate to millennium
SELECT date_trunc(millennium, '2023-04-21 11:12:13.56789');
-- Result: 2001-01-01 00:00:00

-- Truncate to millisecond
SELECT date_trunc(milliseconds, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:13.567000

-- Truncate to microsecond
SELECT date_trunc(microseconds, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:13.567890

date_trunc is an essential function for time‑based analytics in a gbase database. It simplifies aggregating data by fixed intervals and aligning timestamps across different sources. Combine it with date_part or extract for even more control over your time‑series queries in GBASE's MPP cluster.