Posted by: Serge Rielau
Intro

Anyone who has ever attended a talk of mine on SQL compatibility has heard my claim that
"to support Oracle applications in DB2 we had to add a completely new date-arithmetic library of functions since no two functions operating on date appeared to be the same in DB2."
Well, it appears PostgreSQL has its own set of functions yet again.
So, sooner or later a request for the DATE_PART() function in DB2 was bound to pop up.
Not having PostgreSQL available to test for an exact match, here is what I came up with:

DATE_PART(<measure>,<timestamp>)

CREATE OR REPLACE FUNCTION date_part(measure VARCHAR(20), datetime TIMESTAMP)
RETURNS BIGINT
SPECIFIC date_part_timestamp
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE UPPER(measure) 
            WHEN 'CENTURY'      THEN (EXTRACT(YEAR FROM datetime) + 99) / 100
            WHEN 'DAY'          THEN EXTRACT(DAY FROM datetime)
            WHEN 'DECADE'       THEN EXTRACT(YEAR FROM datetime) / 10
            WHEN 'DOW'          THEN DAYOFWEEK(datetime) - 1
            WHEN 'DOY'          THEN DAYOFYEAR(datetime)
            WHEN 'EPOCH'        THEN (DAYS(datetime) - DAYS('1970-01-01')) * 24 * 60 * 60 
                                     + EXTRACT(HOUR FROM datetime)  * 60 * 60
                                     + EXTRACT(MINUTE FROM datetime) * 60
                                     + EXTRACT(SECOND FROM datetime)
            WHEN 'HOUR'         THEN EXTRACT(HOUR FROM datetime)
            WHEN 'MICROSECONDS' THEN EXTRACT(SECOND FROM datetime) * 1000000
            WHEN 'MILLENIUM'    THEN (EXTRACT(YEAR FROM datetime) + 999) / 1000
            WHEN 'MILLISECONDS' THEN EXTRACT(SECOND FROM datetime) * 1000
            WHEN 'MINUTE'       THEN EXTRACT(MINUTE FROM datetime)
            WHEN 'MONTH'        THEN EXTRACT(MONTH FROM datetime)
            WHEN 'QUARTER'      THEN QUARTER(datetime)
            WHEN 'SECOND'       THEN EXTRACT(SECOND FROM datetime)
            WHEN 'WEEK'         THEN WEEK_ISO(datetime)
            WHEN 'YEAR'         THEN EXTRACT(YEAR FROM datetime)
            ELSE                RAISE_ERROR('78000','Unknown measure') 
       END;
/

DATE_PART(<measure>,<time>)

CREATE OR REPLACE FUNCTION date_part(measure VARCHAR(20), datetime TIME)
RETURNS BIGINT
SPECIFIC date_part_time
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE UPPER(measure) 
            WHEN 'HOUR'         THEN EXTRACT(HOUR FROM datetime)
            WHEN 'MICROSECONDS' THEN EXTRACT(SECOND FROM datetime) * 1000000
            WHEN 'MILLISECONDS' THEN EXTRACT(SECOND FROM datetime) * 1000
            WHEN 'MINUTE'       THEN EXTRACT(MINUTE FROM datetime)
            WHEN 'SECOND'       THEN EXTRACT(SECOND FROM datetime)
            ELSE                RAISE_ERROR('78000','Unknown measure') 
       END;
/

Testing

SELECT CURRENT TIMESTAMP AS CURRENT_TIMESTAMP,
       measure,
       date_part(measure, CURRENT TIMESTAMP) AS DATE_PART
  FROM (VALUES 'century',             
               'day',
               'decade',
               'dow',
               'doy',
               'epoch',
               'hour',
               'microseconds',
               'millenium',
               'milliseconds',
               'minute',
               'month',
               'quarter',
               'second',
               'week',
               'year') AS m(measure);

CURRENT_TIMESTAMP          MEASURE                 DATE_PART
-------------------------- ------------ --------------------
2012-12-04 08:02:32.656000 century                        21
2012-12-04 08:02:32.656000 day                             4
2012-12-04 08:02:32.656000 decade                        201
2012-12-04 08:02:32.656000 dow                             2
2012-12-04 08:02:32.656000 doy                           339
2012-12-04 08:02:32.656000 epoch                  1354608152
2012-12-04 08:02:32.656000 hour                            8
2012-12-04 08:02:32.656000 microseconds             32656000
2012-12-04 08:02:32.656000 millenium                       3
2012-12-04 08:02:32.656000 milliseconds                32656
2012-12-04 08:02:32.656000 minute                          2
2012-12-04 08:02:32.656000 month                          12
2012-12-04 08:02:32.656000 quarter                         4
2012-12-04 08:02:32.656000 second                         32
2012-12-04 08:02:32.656000 week                           49
2012-12-04 08:02:32.656000 year                         2012

16 rows were retrieved.

SELECT CURRENT TIME AS CURRENT_TIME,
       measure,
       date_part(measure, CURRENT TIME) AS DATE_PART
  FROM (VALUES 'hour',
               'microseconds',
               'milliseconds',
               'minute',
               'second') AS m(measure);

CURRENT_TIME MEASURE                 DATE_PART
------------ ------------ --------------------
08:05:31     hour                            8
08:05:31     microseconds             31000000
08:05:31     milliseconds                31000
08:05:31     minute                          5
08:05:31     second                         31

Conclusion