|
CREATE OR REPLACE TABLE t1 (a TIME(6));
|
INSERT INTO t1 VALUES ('-24:10:10.10');
|
SELECT
|
EXTRACT(MINUTE FROM a),
|
EXTRACT(SECOND FROM a),
|
EXTRACT(MICROSECOND FROM a),
|
EXTRACT(DAY FROM a),
|
EXTRACT(DAY_HOUR FROM a),
|
EXTRACT(DAY_MINUTE FROM a),
|
EXTRACT(DAY_SECOND FROM a),
|
EXTRACT(DAY_MICROSECOND FROM a)
|
FROM t1
|
\G
|
*************************** 1. row ***************************
|
EXTRACT(MINUTE FROM a): -10
|
EXTRACT(SECOND FROM a): -10
|
EXTRACT(MICROSECOND FROM a): -100000
|
EXTRACT(DAY FROM a): 1
|
EXTRACT(DAY_HOUR FROM a): -100
|
EXTRACT(DAY_MINUTE FROM a): -10010
|
EXTRACT(DAY_SECOND FROM a): -1001010
|
EXTRACT(DAY_MICROSECOND FROM a): -1001010100000
|
The result for EXTRACT(DAY..) looks wrong. It should be -1.
The SQL standard says:
If <extract field> is a <primary datetime field>, then the result is the value of the datetime field identified by that <primary datetime field> and has the same sign as the <extract source>. NOTE 186 — If the value of the identified <primary datetime field> is zero or if <extract source> is not an <interval value expression>, then the sign is irrelevant.
The negative time value '-24:10:10.10' plays role of INTERVAL DAY TO SECOND in this example. The extract source is an <interval value expression>. So the sign is relevant and should be derived from the value.
|