Date Part Functions

The date_part, date_trunc and date_diff functions can be used to extract or manipulate parts of temporal types such as TIMESTAMP, TIMESTAMPTZ, DATE and INTERVAL.

The parts to be extracted or manipulated are specified by one of the strings in the tables below. The example column provides the corresponding parts of the timestamp 2021-08-03 11:59:44.123456. Only the entries of the first table can be extracted from INTERVALs or used to construct them.

Tip

Except for julian and epoch, which return DOUBLEs, all parts are extracted as integers. Since there are no infinite integer values in SereneDB, NULLs are returned for infinite timestamps.

Part Specifiers Usable as Date Part Specifiers and in Intervals

Specifier	Description	Synonyms	Example
century	Gregorian century	cent, centuries, c	21
day	Gregorian day	days, d, dayofmonth	3
decade	Gregorian decade	dec, decades, decs	202
hour	Hours	hr, hours, hrs, h	11
microseconds	Sub-minute microseconds	microsecond, us, usec, usecs, usecond, useconds	44123456
millennium	Gregorian millennium	mil, millenniums, millenia, mils, millenium	3
milliseconds	Sub-minute milliseconds	millisecond, ms, msec, msecs, msecond, mseconds	44123
minute	Minutes	min, minutes, mins, m	59
month	Gregorian month	mon, months, mons	8
quarter	Quarter of the year (1-4)	quarters	3
second	Seconds	sec, seconds, secs, s	44
year	Gregorian year	yr, y, years, yrs	2021

Part Specifiers Only Usable as Date Part Specifiers

Specifier	Description	Synonyms	Example
dayofweek	Day of the week (Sunday = 0, Saturday = 6)	weekday, dow	2
dayofyear	Day of the year (1-365/366)	doy	215
epoch	Seconds since 1970-01-01		1627991984.123456
era	Gregorian era (CE/AD, BCE/BC)		1
isodow	ISO day of the week (Monday = 1, Sunday = 7)		2
isoyear	ISO Year number (Starts on Monday of week containing Jan 4th)		2021
julian	Julian Day number.		2459430.4998162435
timezone_hour	Time zone offset hour portion		0
timezone_minute	Time zone offset minute portion		0
timezone	Time zone offset in seconds		0
week	Week number	weeks, w	31
yearweek	ISO year and week number in YYYYWW format		202131

Part Functions

There are dedicated extraction functions to get certain subfields:

Name	Description
century(date)	Century.
day(date)	Day.
dayofmonth(date)	Day (synonym).
dayofweek(date)	Numeric weekday (Sunday = 0, Saturday = 6).
dayofyear(date)	Day of the year (starts from 1, i.e., January 1 = 1).
decade(date)	Decade (year / 10).
epoch(date)	Seconds since 1970-01-01.
era(date)	Calendar era.
hour(date)	Hours.
isodow(date)	Numeric ISO weekday (Monday = 1, Sunday = 7).
isoyear(date)	ISO Year number (Starts on Monday of week containing Jan 4th).
julian(date)	DOUBLE Julian Day number.
microsecond(date)	Sub-minute microseconds.
millennium(date)	Millennium.
millisecond(date)	Sub-minute milliseconds.
minute(date)	Minutes.
month(date)	Month.
quarter(date)	Quarter.
second(date)	Seconds.
timezone_hour(date)	Time zone offset hour portion.
timezone_minute(date)	Time zone offset minutes portion.
timezone(date)	Time zone offset in seconds.
week(date)	ISO Week.
weekday(date)	Numeric weekday synonym (Sunday = 0, Saturday = 6).
weekofyear(date)	ISO Week (synonym).
year(date)	Year.
yearweek(date)	BIGINT of combined ISO Year number and 2-digit version of ISO Week number.

century(date)

Century.

Query

SELECT century(DATE '1992-02-15') AS century;

Result

 century---------      20

day(date)

Day.

Query

SELECT day(DATE '1992-02-15') AS day;

Result

 day-----  15

dayofmonth(date)

Day (synonym).

Query

SELECT dayofmonth(DATE '1992-02-15') AS dayofmonth;

Result

 dayofmonth------------         15

dayofweek(date)

Numeric weekday (Sunday = 0, Saturday = 6).

Query

SELECT dayofweek(DATE '1992-02-15') AS dayofweek;

Result

 dayofweek-----------         6

dayofyear(date)

Day of the year (starts from 1, i.e., January 1 = 1).

Query

SELECT dayofyear(DATE '1992-02-15') AS dayofyear;

Result

 dayofyear-----------        46

decade(date)

Decade (year / 10).

Query

SELECT decade(DATE '1992-02-15') AS decade;

Result

 decade--------    199

epoch(date)

Seconds since 1970-01-01.

Query

SELECT epoch(DATE '1992-02-15') AS epoch;

Result

 epoch----------- 698112000

era(date)

Calendar era.

Query

SELECT era(DATE '0044-03-15 (BC)') AS era;

Result

 era-----   0

hour(date)

Hours.

Query

SELECT hour(timestamp '2021-08-03 11:59:44.123456') AS hour;

Result

 hour------   11

isodow(date)

Numeric ISO weekday (Monday = 1, Sunday = 7).

Query

SELECT isodow(DATE '1992-02-15') AS isodow;

Result

 isodow--------      6

isoyear(date)

ISO Year number (Starts on Monday of week containing Jan 4th).

Query

SELECT isoyear(DATE '2022-01-01') AS isoyear;

Result

 isoyear---------    2021

julian(date)

DOUBLE Julian Day number.

Query

SELECT julian(DATE '1992-09-20') AS julian;

Result

 julian--------- 2448886

microsecond(date)

Sub-minute microseconds.

Query

SELECT microsecond(timestamp '2021-08-03 11:59:44.123456') AS microsecond;

Result

 microsecond-------------    44123456

millennium(date)

Millennium.

Query

SELECT millennium(DATE '1992-02-15') AS millennium;

Result

 millennium------------          2

millisecond(date)

Sub-minute milliseconds.

Query

SELECT millisecond(timestamp '2021-08-03 11:59:44.123456') AS millisecond;

Result

 millisecond-------------       44123

minute(date)

Minutes.

Query

SELECT minute(timestamp '2021-08-03 11:59:44.123456') AS minute;

Result

 minute--------     59

month(date)

Month.

Query

SELECT month(DATE '1992-02-15') AS month;

Result

 month-------     2

quarter(date)

Quarter.

Query

SELECT quarter(DATE '1992-02-15') AS quarter;

Result

 quarter---------       1

second(date)

Seconds.

Query

SELECT second(timestamp '2021-08-03 11:59:44.123456') AS second;

Result

 second--------     44

timezone_hour(date)

Time zone offset hour portion.

Query

SELECT timezone_hour(TIMESTAMP '2021-08-03 11:59:44.123456') AS timezone_hour;

Result

 timezone_hour---------------             0

timezone_minute(date)

Time zone offset minutes portion.

Query

SELECT timezone_minute(TIMESTAMP '2021-08-03 11:59:44.123456') AS timezone_minute;

Result

 timezone_minute-----------------               0

timezone(date)

Time zone offset in seconds.

Query

SELECT timezone(TIMESTAMP '2021-08-03 11:59:44.123456') AS timezone;

Result

 timezone----------        0

week(date)

ISO Week.

Query

SELECT week(DATE '1992-02-15') AS week;

Result

 week------    7

weekday(date)

Numeric weekday synonym (Sunday = 0, Saturday = 6).

Query

SELECT weekday(DATE '1992-02-15') AS weekday;

Result

 weekday---------       6

weekofyear(date)

ISO Week (synonym).

Query

SELECT weekofyear(DATE '1992-02-15') AS weekofyear;

Result

 weekofyear------------          7

year(date)

Year.

Query

SELECT year(DATE '1992-02-15') AS year;

Result

 year------ 1992

yearweek(date)

BIGINT of combined ISO Year number and 2-digit version of ISO Week number.

Query

SELECT yearweek(DATE '1992-02-15') AS yearweek;

Result

 yearweek----------   199207