GBase 8a date_part and extract: Extracting Date/Time Components

GBase 8a provides two functions for extracting specific parts of a date or time value: date_part and extract. They are equivalent in functionality — date_part has a simpler syntax, while extract follows the Oracle‑compatible style. Both support a wide range of extraction types, from year and month down to microseconds.

Syntax

-- date_part: type can be quoted or unquoted
date_part(type, date)

-- extract
extract(type FROM date)

• type: the component to extract, e.g. year, month, day.
• date: a datetime expression (usually DATETIME; DATE values are auto‑cast).

Quick Reference of Common Types

type	Description	Example Output
year	Year	2023
month	Month	4
day	Day	23
hour	Hour	11
minute	Minute	12
second	Second	13
quarter	Quarter (1–4)	2
week	Week of year	17
dow	Day of week (Sunday=0)	0
isodow	ISO day of week (Monday=1, Sunday=7)	7
doy	Day of year	113
epoch	Unix timestamp (with microseconds)	1682219533.56789
year_month	Year and month	202304
day_hour	Day and hour	2311
hour_minute	Hour and minute	1112
minute_second	Minute and second	1213
millisecond	Millisecond (integer)	567
microsecond	Microsecond (integer)	567890
century	Century	21
decade	Decade (year ÷ 10)	202
millennium	Millennium	3
milliseconds	Seconds + milliseconds (decimal)	13567.89
microseconds	Seconds + microseconds (integer)	13567890

Typical Examples

All examples use date_part; extract produces identical results.

-- Year
SELECT date_part(year, '2023-04-23 11:12:13.56789');      -- 2023

-- Month
SELECT date_part(month, '2023-04-23 11:12:13.56789');     -- 4

-- Day
SELECT date_part(day, '2023-04-23 11:12:13.56789');       -- 23

-- Hour
SELECT date_part(hour, '2023-04-23 11:12:13.56789');      -- 11

-- Minute
SELECT date_part(minute, '2023-04-23 11:12:13.56789');    -- 12

-- Second
SELECT date_part(second, '2023-04-23 11:12:13.56789');    -- 13

-- Millisecond (truncates the fractional seconds)
SELECT date_part(millisecond, '2023-04-23 11:12:13.56789'); -- 567

-- Microsecond
SELECT date_part(microsecond, '2023-04-23 11:12:13.56789'); -- 567890

-- Year and month as an integer (202304)
SELECT date_part(year_month, '2023-04-23 11:12:13.56789');

-- Day and hour as an integer (2311)
SELECT date_part(day_hour, '2023-04-23 11:12:13.56789');

-- Hour and minute as an integer (1112)
SELECT date_part(hour_minute, '2023-04-23 11:12:13.56789');

-- Minute and second as an integer (1213)
SELECT date_part(minute_second, '2023-04-23 11:12:13.56789');

-- Epoch with microseconds (extract only)
SELECT extract(epoch FROM '2023-04-23 11:12:13.56789');    -- 1682219533.56789

-- Milliseconds as decimal (13.56789 seconds)
SELECT date_part(milliseconds, '2023-04-23 11:12:13.56789'); -- 13567.89

-- Microseconds as integer (13.56789 seconds → 13567890 µs)
SELECT date_part(microseconds, '2023-04-23 11:12:13.56789'); -- 13567890

-- ISO year vs. calendar year
SELECT date_part(isoyear, '2023-01-01 11:12:13');          -- 2022
SELECT date_part(year, '2023-01-01 11:12:13');             -- 2023

With these two functions, extracting any component from a date or time value becomes straightforward. They are essential for grouping, filtering, and reporting in a gbase database.