Netezza Extract Function Usage and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:5 mins read

Netezza extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. This function is equivalent to Netezza date_part() function.

netezza extract functionNetezza Extract Function Syntax

extract(units FROM col)

Netezza Extract Function Usage

Below table represents the descriptions of the different units used in extract function:

Unit Value Description
epoch The number of seconds since 1970-01-01 00:00:00-00. The value can be positive or negative.
millennium/millenniums The millennium value.
century/centuries The number of full 100-year periods represented by the year.
decade/decades The number of full 10-year periods represented by the year.
year/years The year
quarter The quarter of the year (1 – 4) that the specified day is in.
month/months The number of the month within the year (1 – 12).
week The number of the week of the year (1 – 53) that the specified day is in.
day/days The day of the month. Between (1 – 31).
dow The day of the week, from 1 (Sunday) to 7 (Saturday).
doy The day of the year (1 – 366).
hour/hours The hour of the day (0 – 23).
minute/minutes The minute of the hour (0 – 59).
second/seconds The second of the minute, not including fractional parts (0 – 59).
millisecond/milliseconds The second of the minute, including fractional parts to one thousandth of a second, multiplied by 1000 (0 – 59999).
microsecond/microseconds The second of the minute, including fractional parts to one millionth of a second, multiplied by 1000000 (0 – 59999999).

Netezza Date Extract Examples

Netezza extract epoch from date:

TRAINING.ADMIN(ADMIN)=> select extract(epoch from now());
 date_part
------------------
 1473903098.45085
(1 row)

Netezza extract millennium from date:

TRAINING.ADMIN(ADMIN)=> select extract(millennium from now());
 date_part
-----------
 3
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(millenniums from now());
 date_part
-----------
 3
(1 row)

Netezza extract century from date:

TRAINING.ADMIN(ADMIN)=> select extract(century from now());
 date_part
-----------
 21
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(centuries from now());
 date_part
-----------
 21
(1 row)

Netezza extract decade from date:

TRAINING.ADMIN(ADMIN)=> select extract(decade from now());
 date_part
-----------
 201
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(decades from now());
 date_part
-----------
 201
(1 row)

Netezza extract year from date:

TRAINING.ADMIN(ADMIN)=> select extract(year from now());
 date_part
-----------
 2016
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(years from now());
 date_part
-----------
 2016
(1 row)

Netezza extract quarter from date:

TRAINING.ADMIN(ADMIN)=> select extract(quarter from now());
 date_part
-----------
 3
(1 row)

Netezza extract month from date:

TRAINING.ADMIN(ADMIN)=> select extract(month from now());
 date_part
-----------
 9
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(months from now());
 date_part
-----------
 9
(1 row)

Netezza extract week from date:

TRAINING.ADMIN(ADMIN)=> select extract(week from now());
 date_part
-----------
 37
(1 row)

Netezza extract day from date:

TRAINING.ADMIN(ADMIN)=> select extract(day from now());
 date_part
-----------
 14
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(days from now());
 date_part
-----------
 14
(1 row)

Netezza extract day of the week from date:

TRAINING.ADMIN(ADMIN)=> select extract(dow from now());
 date_part
-----------
 3
(1 row)

Netezza extract day of the year from date:

TRAINING.ADMIN(ADMIN)=> select extract(doy from now());
 date_part
-----------
 258
(1 row)

Netezza extract hours from date:

TRAINING.ADMIN(ADMIN)=> select extract(hour from now());
 date_part
-----------
 18
(1 row)

TRAINING.ADMIN(ADMIN)=> select extract(hours from now());
 date_part
-----------
 18
(1 row)

Netezza extract seconds from date:

TRAINING.ADMIN(ADMIN)=> select extract(second from now());
 date_part
-----------
 54.350246
(1 row)

Netezza extract milliseconds from date:

TRAINING.ADMIN(ADMIN)=> select extract(millisecond from now());
 date_part
-----------
 2757.6
(1 row)

Netezza extract microseconds from date:

TRAINING.ADMIN(ADMIN)=> select extract(microsecond from now());
 date_part
-----------
 9853649
(1 row)
TRAINING.ADMIN(ADMIN)=>

Read:

Tags: , ,