datepart()

Extracts a specified date component from a date and returns it as an integer.

Syntax

datepart(DATE, DATEPART)

Parameters

DATE: An expression that returns a date.
DATEPART: A constant string representing the date component to extract. The supported values are as follows.

Type indicator	Return type	Description	Example value
`century`	integer	Century	21
`day`	integer	Day of month (1–31)	12
`decade`	integer	Year divided by 10	201
`dow`	integer	Day of week, Sunday (0) through Saturday (6)	1
`doy`	integer	Day of year	163
`epoch`	long	Seconds elapsed since January 1, 1970	1497269156
`hour`	integer	Hour of day in 24-hour format (0–23)	21
`isodow`	integer	ISO 8601 day of week, Monday (1) through Sunday (7)	1
`isoyear`	integer	ISO 8601 year (the year containing the first Thursday of that week)	2017
`microseconds`	integer	Seconds and microseconds within the current minute combined (milliseconds × 1000)	56371000
`millennium`	integer	Millennium (1000-year unit)	3
`milliseconds`	integer	Seconds and milliseconds within the current minute combined	56371
`min`, `minute`	integer	Minutes (0–59)	5
`mon`, `month`	integer	Month (1–12)	6
`msec`	integer	Milliseconds within the current second (milliseconds % 1000)	377
`quarter`	integer	Quarter of the year (1–4)	2
`sec`, `second`	integer	Seconds (0–59)	56
`timezone`	integer	Time zone offset from UTC in seconds	32400
`timezone_hour`	integer	Time zone offset from UTC in hours	9
`timezone_minute`	integer	Minutes portion of the time zone offset from UTC (excluding hours)	0
`week`	integer	ISO 8601 week number of the year	24
`year`	integer	Year	2017

Description

The datepart() function extracts the component specified by DATEPART from DATE. When epoch is specified, the function returns a 64-bit integer (long); otherwise it returns a 32-bit integer.

Returns null if DATE is null or not a timestamp type.

Error codes

90880: Raised when DATEPART is null or not a string.
90881: Raised when the DATEPART value is invalid. The type parameter contains the invalid type indicator.

Usage examples

To prepare the WEB_APACHE_SAMPLE table used in these examples, refer to Preparing sample data.

Extract the hour from the access log timestamp.

table limit=5 WEB_APACHE_SAMPLE | eval result = datepart(_time, "hour")
| fields _time, result

Extract the month from the access log timestamp.

table limit=5 WEB_APACHE_SAMPLE | eval result = datepart(_time, "mon")
| fields _time, result

Extract the Unix epoch value from the access log timestamp.

table limit=5 WEB_APACHE_SAMPLE | eval result = datepart(_time, "epoch")
| fields _time, result

NULL input

json "{}" | eval result = datepart(null, "year")
| # result: null

Compatibility

The datepart() function has been available since before Sonar 4.0.