diff options
Diffstat (limited to 'src')
22 files changed, 1323 insertions, 493 deletions
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 8b1bad0d794..05cc2c9ae0d 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -14020,7 +14020,7 @@ func_expr_common_subexpr: { $$ = makeTypeCast($3, $5, @1); } | EXTRACT '(' extract_list ')' { - $$ = (Node *) makeFuncCall(SystemFuncName("date_part"), + $$ = (Node *) makeFuncCall(SystemFuncName("extract"), $3, COERCE_SQL_SYNTAX, @1); diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 6053d0e8a6f..83036e5985e 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -31,6 +31,7 @@ #include "utils/builtins.h" #include "utils/date.h" #include "utils/datetime.h" +#include "utils/numeric.h" #include "utils/sortsupport.h" /* @@ -1063,6 +1064,182 @@ in_range_date_interval(PG_FUNCTION_ARGS) } +/* extract_date() + * Extract specified field from date type. + */ +Datum +extract_date(PG_FUNCTION_ARGS) +{ + text *units = PG_GETARG_TEXT_PP(0); + DateADT date = PG_GETARG_DATEADT(1); + int64 intresult; + int type, + val; + char *lowunits; + int year, + mon, + mday; + + lowunits = downcase_truncate_identifier(VARDATA_ANY(units), + VARSIZE_ANY_EXHDR(units), + false); + + type = DecodeUnits(0, lowunits, &val); + if (type == UNKNOWN_FIELD) + type = DecodeSpecial(0, lowunits, &val); + + if (DATE_NOT_FINITE(date) && (type == UNITS || type == RESERV)) + { + switch (val) + { + /* Oscillating units */ + case DTK_DAY: + case DTK_MONTH: + case DTK_QUARTER: + case DTK_WEEK: + case DTK_DOW: + case DTK_ISODOW: + case DTK_DOY: + PG_RETURN_NULL(); + break; + + /* Monotonically-increasing units */ + case DTK_YEAR: + case DTK_DECADE: + case DTK_CENTURY: + case DTK_MILLENNIUM: + case DTK_JULIAN: + case DTK_ISOYEAR: + case DTK_EPOCH: + if (DATE_IS_NOBEGIN(date)) + PG_RETURN_NUMERIC(DatumGetNumeric(DirectFunctionCall3(numeric_in, + CStringGetDatum("-Infinity"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)))); + else + PG_RETURN_NUMERIC(DatumGetNumeric(DirectFunctionCall3(numeric_in, + CStringGetDatum("Infinity"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)))); + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("date units \"%s\" not supported", + lowunits))); + } + } + else if (type == UNITS) + { + j2date(date + POSTGRES_EPOCH_JDATE, &year, &mon, &mday); + + switch (val) + { + case DTK_DAY: + intresult = mday; + break; + + case DTK_MONTH: + intresult = mon; + break; + + case DTK_QUARTER: + intresult = (mon - 1) / 3 + 1; + break; + + case DTK_WEEK: + intresult = date2isoweek(year, mon, mday); + break; + + case DTK_YEAR: + if (year > 0) + intresult = year; + else + /* there is no year 0, just 1 BC and 1 AD */ + intresult = year - 1; + break; + + case DTK_DECADE: + /* see comments in timestamp_part */ + if (year >= 0) + intresult = year / 10; + else + intresult = -((8 - (year - 1)) / 10); + break; + + case DTK_CENTURY: + /* see comments in timestamp_part */ + if (year > 0) + intresult = (year + 99) / 100; + else + intresult = -((99 - (year - 1)) / 100); + break; + + case DTK_MILLENNIUM: + /* see comments in timestamp_part */ + if (year > 0) + intresult = (year + 999) / 1000; + else + intresult = -((999 - (year - 1)) / 1000); + break; + + case DTK_JULIAN: + intresult = date + POSTGRES_EPOCH_JDATE; + break; + + case DTK_ISOYEAR: + intresult = date2isoyear(year, mon, mday); + /* Adjust BC years */ + if (intresult <= 0) + intresult -= 1; + break; + + case DTK_DOW: + case DTK_ISODOW: + intresult = j2day(date + POSTGRES_EPOCH_JDATE); + if (val == DTK_ISODOW && intresult == 0) + intresult = 7; + break; + + case DTK_DOY: + intresult = date2j(year, mon, mday) - date2j(year, 1, 1) + 1; + break; + + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("date units \"%s\" not supported", + lowunits))); + intresult = 0; + } + } + else if (type == RESERV) + { + switch (val) + { + case DTK_EPOCH: + intresult = ((int64) date + POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY; + break; + + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("date units \"%s\" not supported", + lowunits))); + intresult = 0; + } + } + else + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("date units \"%s\" not recognized", lowunits))); + intresult = 0; + } + + PG_RETURN_NUMERIC(int64_to_numeric(intresult)); +} + + /* Add an interval to a date, giving a new date. * Must handle both positive and negative intervals. * @@ -1949,15 +2126,15 @@ in_range_time_interval(PG_FUNCTION_ARGS) } -/* time_part() +/* time_part() and extract_time() * Extract specified field from time type. */ -Datum -time_part(PG_FUNCTION_ARGS) +static Datum +time_part_common(PG_FUNCTION_ARGS, bool retnumeric) { text *units = PG_GETARG_TEXT_PP(0); TimeADT time = PG_GETARG_TIMEADT(1); - float8 result; + int64 intresult; int type, val; char *lowunits; @@ -1981,23 +2158,37 @@ time_part(PG_FUNCTION_ARGS) switch (val) { case DTK_MICROSEC: - result = tm->tm_sec * 1000000.0 + fsec; + intresult = tm->tm_sec * 1000000 + fsec; break; case DTK_MILLISEC: - result = tm->tm_sec * 1000.0 + fsec / 1000.0; + if (retnumeric) + /*--- + * tm->tm_sec * 1000 + fsec / 1000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 3)); + else + PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0); break; case DTK_SECOND: - result = tm->tm_sec + fsec / 1000000.0; + if (retnumeric) + /*--- + * tm->tm_sec + fsec / 1'000'000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 6)); + else + PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0); break; case DTK_MINUTE: - result = tm->tm_min; + intresult = tm->tm_min; break; case DTK_HOUR: - result = tm->tm_hour; + intresult = tm->tm_hour; break; case DTK_TZ: @@ -2016,12 +2207,15 @@ time_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("\"time\" units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } } else if (type == RESERV && val == DTK_EPOCH) { - result = time / 1000000.0; + if (retnumeric) + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(time, 6)); + else + PG_RETURN_FLOAT8(time / 1000000.0); } else { @@ -2029,10 +2223,25 @@ time_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("\"time\" units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } - PG_RETURN_FLOAT8(result); + if (retnumeric) + PG_RETURN_NUMERIC(int64_to_numeric(intresult)); + else + PG_RETURN_FLOAT8(intresult); +} + +Datum +time_part(PG_FUNCTION_ARGS) +{ + return time_part_common(fcinfo, false); +} + +Datum +extract_time(PG_FUNCTION_ARGS) +{ + return time_part_common(fcinfo, true); } @@ -2686,15 +2895,15 @@ datetimetz_timestamptz(PG_FUNCTION_ARGS) } -/* timetz_part() +/* timetz_part() and extract_timetz() * Extract specified field from time type. */ -Datum -timetz_part(PG_FUNCTION_ARGS) +static Datum +timetz_part_common(PG_FUNCTION_ARGS, bool retnumeric) { text *units = PG_GETARG_TEXT_PP(0); TimeTzADT *time = PG_GETARG_TIMETZADT_P(1); - float8 result; + int64 intresult; int type, val; char *lowunits; @@ -2709,7 +2918,6 @@ timetz_part(PG_FUNCTION_ARGS) if (type == UNITS) { - double dummy; int tz; fsec_t fsec; struct pg_tm tt, @@ -2720,38 +2928,49 @@ timetz_part(PG_FUNCTION_ARGS) switch (val) { case DTK_TZ: - result = -tz; + intresult = -tz; break; case DTK_TZ_MINUTE: - result = -tz; - result /= SECS_PER_MINUTE; - FMODULO(result, dummy, (double) MINS_PER_HOUR); + intresult = (-tz / SECS_PER_MINUTE) % MINS_PER_HOUR; break; case DTK_TZ_HOUR: - dummy = -tz; - FMODULO(dummy, result, (double) SECS_PER_HOUR); + intresult = -tz / SECS_PER_HOUR; break; case DTK_MICROSEC: - result = tm->tm_sec * 1000000.0 + fsec; + intresult = tm->tm_sec * 1000000 + fsec; break; case DTK_MILLISEC: - result = tm->tm_sec * 1000.0 + fsec / 1000.0; + if (retnumeric) + /*--- + * tm->tm_sec * 1000 + fsec / 1000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 3)); + else + PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0); break; case DTK_SECOND: - result = tm->tm_sec + fsec / 1000000.0; + if (retnumeric) + /*--- + * tm->tm_sec + fsec / 1'000'000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 6)); + else + PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0); break; case DTK_MINUTE: - result = tm->tm_min; + intresult = tm->tm_min; break; case DTK_HOUR: - result = tm->tm_hour; + intresult = tm->tm_hour; break; case DTK_DAY: @@ -2766,12 +2985,19 @@ timetz_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("\"time with time zone\" units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } } else if (type == RESERV && val == DTK_EPOCH) { - result = time->time / 1000000.0 + time->zone; + if (retnumeric) + /*--- + * time->time / 1'000'000 + time->zone + * = (time->time + time->zone * 1'000'000) / 1'000'000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(time->time + time->zone * 1000000LL, 6)); + else + PG_RETURN_FLOAT8(time->time / 1000000.0 + time->zone); } else { @@ -2779,10 +3005,26 @@ timetz_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("\"time with time zone\" units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } - PG_RETURN_FLOAT8(result); + if (retnumeric) + PG_RETURN_NUMERIC(int64_to_numeric(intresult)); + else + PG_RETURN_FLOAT8(intresult); +} + + +Datum +timetz_part(PG_FUNCTION_ARGS) +{ + return timetz_part_common(fcinfo, false); +} + +Datum +extract_timetz(PG_FUNCTION_ARGS) +{ + return timetz_part_common(fcinfo, true); } /* timetz_zone() diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c index 682200f636b..9525ade1f7c 100644 --- a/src/backend/utils/adt/numeric.c +++ b/src/backend/utils/adt/numeric.c @@ -4092,6 +4092,67 @@ int64_to_numeric(int64 val) return res; } +/* + * Convert val1/(10**val2) to numeric. This is much faster than normal + * numeric division. + */ +Numeric +int64_div_fast_to_numeric(int64 val1, int log10val2) +{ + Numeric res; + NumericVar result; + int64 saved_val1 = val1; + int w; + int m; + + /* how much to decrease the weight by */ + w = log10val2 / DEC_DIGITS; + /* how much is left */ + m = log10val2 % DEC_DIGITS; + + /* + * If there is anything left, multiply the dividend by what's left, then + * shift the weight by one more. + */ + if (m > 0) + { + static int pow10[] = {1, 10, 100, 1000}; + + StaticAssertStmt(lengthof(pow10) == DEC_DIGITS, "mismatch with DEC_DIGITS"); + if (unlikely(pg_mul_s64_overflow(val1, pow10[DEC_DIGITS - m], &val1))) + { + /* + * If it doesn't fit, do the whole computation in numeric the slow + * way. Note that va1l may have been overwritten, so use + * saved_val1 instead. + */ + int val2 = 1; + + for (int i = 0; i < log10val2; i++) + val2 *= 10; + res = numeric_div_opt_error(int64_to_numeric(saved_val1), int64_to_numeric(val2), NULL); + res = DatumGetNumeric(DirectFunctionCall2(numeric_round, + NumericGetDatum(res), + Int32GetDatum(log10val2))); + return res; + } + w++; + } + + init_var(&result); + + int64_to_numericvar(val1, &result); + + result.weight -= w; + result.dscale += w * DEC_DIGITS - (DEC_DIGITS - m); + + res = make_result(&result); + + free_var(&result); + + return res; +} + Datum int4_numeric(PG_FUNCTION_ARGS) { diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 254e8f30501..0b5314e49b3 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -9782,6 +9782,27 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context) appendStringInfoString(buf, "))"); return true; + case F_EXTRACT_TEXT_DATE: + case F_EXTRACT_TEXT_TIME: + case F_EXTRACT_TEXT_TIMETZ: + case F_EXTRACT_TEXT_TIMESTAMP: + case F_EXTRACT_TEXT_TIMESTAMPTZ: + case F_EXTRACT_TEXT_INTERVAL: + /* EXTRACT (x FROM y) */ + appendStringInfoString(buf, "EXTRACT("); + { + Const *con = (Const *) linitial(expr->args); + + Assert(IsA(con, Const) && + con->consttype == TEXTOID && + !con->constisnull); + appendStringInfoString(buf, TextDatumGetCString(con->constvalue)); + } + appendStringInfoString(buf, " FROM "); + get_rule_expr((Node *) lsecond(expr->args), context, false); + appendStringInfoChar(buf, ')'); + return true; + case F_IS_NORMALIZED: /* IS xxx NORMALIZED */ appendStringInfoString(buf, "(("); diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 194861f19e3..b2bdbcab576 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -22,6 +22,7 @@ #include "access/xact.h" #include "catalog/pg_type.h" +#include "common/int.h" #include "common/int128.h" #include "funcapi.h" #include "libpq/pqformat.h" @@ -35,6 +36,7 @@ #include "utils/date.h" #include "utils/datetime.h" #include "utils/float.h" +#include "utils/numeric.h" /* * gcc's -ffast-math switch breaks routines that expect exact results from @@ -3991,8 +3993,8 @@ timestamptz_bin(PG_FUNCTION_ARGS) { Interval *stride = PG_GETARG_INTERVAL_P(0); TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1); - TimestampTz origin = PG_GETARG_TIMESTAMPTZ(2); - TimestampTz result, + TimestampTz origin = PG_GETARG_TIMESTAMPTZ(2); + TimestampTz result, stride_usecs, tm_diff, tm_delta; @@ -4597,15 +4599,15 @@ NonFiniteTimestampTzPart(int type, int unit, char *lowunits, } } -/* timestamp_part() +/* timestamp_part() and extract_timestamp() * Extract specified field from timestamp. */ -Datum -timestamp_part(PG_FUNCTION_ARGS) +static Datum +timestamp_part_common(PG_FUNCTION_ARGS, bool retnumeric) { text *units = PG_GETARG_TEXT_PP(0); Timestamp timestamp = PG_GETARG_TIMESTAMP(1); - float8 result; + int64 intresult; Timestamp epoch; int type, val; @@ -4624,11 +4626,28 @@ timestamp_part(PG_FUNCTION_ARGS) if (TIMESTAMP_NOT_FINITE(timestamp)) { - result = NonFiniteTimestampTzPart(type, val, lowunits, - TIMESTAMP_IS_NOBEGIN(timestamp), - false); - if (result) - PG_RETURN_FLOAT8(result); + double r = NonFiniteTimestampTzPart(type, val, lowunits, + TIMESTAMP_IS_NOBEGIN(timestamp), + false); + + if (r) + { + if (retnumeric) + { + if (r < 0) + return DirectFunctionCall3(numeric_in, + CStringGetDatum("-Infinity"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + else if (r > 0) + return DirectFunctionCall3(numeric_in, + CStringGetDatum("Infinity"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + } + else + PG_RETURN_FLOAT8(r); + } else PG_RETURN_NULL(); } @@ -4643,47 +4662,61 @@ timestamp_part(PG_FUNCTION_ARGS) switch (val) { case DTK_MICROSEC: - result = tm->tm_sec * 1000000.0 + fsec; + intresult = tm->tm_sec * 1000000.0 + fsec; break; case DTK_MILLISEC: - result = tm->tm_sec * 1000.0 + fsec / 1000.0; + if (retnumeric) + /*--- + * tm->tm_sec * 1000 + fsec / 1000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 3)); + else + PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0); break; case DTK_SECOND: - result = tm->tm_sec + fsec / 1000000.0; + if (retnumeric) + /*--- + * tm->tm_sec + fsec / 1'000'000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 6)); + else + PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0); break; case DTK_MINUTE: - result = tm->tm_min; + intresult = tm->tm_min; break; case DTK_HOUR: - result = tm->tm_hour; + intresult = tm->tm_hour; break; case DTK_DAY: - result = tm->tm_mday; + intresult = tm->tm_mday; break; case DTK_MONTH: - result = tm->tm_mon; + intresult = tm->tm_mon; break; case DTK_QUARTER: - result = (tm->tm_mon - 1) / 3 + 1; + intresult = (tm->tm_mon - 1) / 3 + 1; break; case DTK_WEEK: - result = (float8) date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday); + intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday); break; case DTK_YEAR: if (tm->tm_year > 0) - result = tm->tm_year; + intresult = tm->tm_year; else /* there is no year 0, just 1 BC and 1 AD */ - result = tm->tm_year - 1; + intresult = tm->tm_year - 1; break; case DTK_DECADE: @@ -4694,9 +4727,9 @@ timestamp_part(PG_FUNCTION_ARGS) * is 11 BC thru 2 BC... */ if (tm->tm_year >= 0) - result = tm->tm_year / 10; + intresult = tm->tm_year / 10; else - result = -((8 - (tm->tm_year - 1)) / 10); + intresult = -((8 - (tm->tm_year - 1)) / 10); break; case DTK_CENTURY: @@ -4708,43 +4741,50 @@ timestamp_part(PG_FUNCTION_ARGS) * ---- */ if (tm->tm_year > 0) - result = (tm->tm_year + 99) / 100; + intresult = (tm->tm_year + 99) / 100; else /* caution: C division may have negative remainder */ - result = -((99 - (tm->tm_year - 1)) / 100); + intresult = -((99 - (tm->tm_year - 1)) / 100); break; case DTK_MILLENNIUM: /* see comments above. */ if (tm->tm_year > 0) - result = (tm->tm_year + 999) / 1000; + intresult = (tm->tm_year + 999) / 1000; else - result = -((999 - (tm->tm_year - 1)) / 1000); + intresult = -((999 - (tm->tm_year - 1)) / 1000); break; case DTK_JULIAN: - result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday); - result += ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + - tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY; + if (retnumeric) + PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)), + numeric_div_opt_error(int64_to_numeric(((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * 1000000LL + fsec), + int64_to_numeric(SECS_PER_DAY * 1000000LL), + NULL), + NULL)); + else + PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + + ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + + tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY); break; case DTK_ISOYEAR: - result = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday); + intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday); /* Adjust BC years */ - if (result <= 0) - result -= 1; + if (intresult <= 0) + intresult -= 1; break; case DTK_DOW: case DTK_ISODOW: - result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)); - if (val == DTK_ISODOW && result == 0) - result = 7; + intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)); + if (val == DTK_ISODOW && intresult == 0) + intresult = 7; break; case DTK_DOY: - result = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - - date2j(tm->tm_year, 1, 1) + 1); + intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + - date2j(tm->tm_year, 1, 1) + 1); break; case DTK_TZ: @@ -4755,7 +4795,7 @@ timestamp_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("timestamp units \"%s\" not supported", lowunits))); - result = 0; + intresult = 0; } } else if (type == RESERV) @@ -4764,11 +4804,37 @@ timestamp_part(PG_FUNCTION_ARGS) { case DTK_EPOCH: epoch = SetEpochTimestamp(); - /* try to avoid precision loss in subtraction */ - if (timestamp < (PG_INT64_MAX + epoch)) - result = (timestamp - epoch) / 1000000.0; + /* (timestamp - epoch) / 1000000 */ + if (retnumeric) + { + Numeric result; + + if (timestamp < (PG_INT64_MAX + epoch)) + result = int64_div_fast_to_numeric(timestamp - epoch, 6); + else + { + result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp), + int64_to_numeric(epoch), + NULL), + int64_to_numeric(1000000), + NULL); + result = DatumGetNumeric(DirectFunctionCall2(numeric_round, + NumericGetDatum(result), + Int32GetDatum(6))); + } + PG_RETURN_NUMERIC(result); + } else - result = ((float8) timestamp - epoch) / 1000000.0; + { + float8 result; + + /* try to avoid precision loss in subtraction */ + if (timestamp < (PG_INT64_MAX + epoch)) + result = (timestamp - epoch) / 1000000.0; + else + result = ((float8) timestamp - epoch) / 1000000.0; + PG_RETURN_FLOAT8(result); + } break; default: @@ -4776,7 +4842,7 @@ timestamp_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("timestamp units \"%s\" not supported", lowunits))); - result = 0; + intresult = 0; } } @@ -4785,27 +4851,41 @@ timestamp_part(PG_FUNCTION_ARGS) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("timestamp units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } - PG_RETURN_FLOAT8(result); + if (retnumeric) + PG_RETURN_NUMERIC(int64_to_numeric(intresult)); + else + PG_RETURN_FLOAT8(intresult); +} + +Datum +timestamp_part(PG_FUNCTION_ARGS) +{ + return timestamp_part_common(fcinfo, false); } -/* timestamptz_part() +Datum +extract_timestamp(PG_FUNCTION_ARGS) +{ + return timestamp_part_common(fcinfo, true); +} + +/* timestamptz_part() and extract_timestamptz() * Extract specified field from timestamp with time zone. */ -Datum -timestamptz_part(PG_FUNCTION_ARGS) +static Datum +timestamptz_part_common(PG_FUNCTION_ARGS, bool retnumeric) { text *units = PG_GETARG_TEXT_PP(0); TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1); - float8 result; + int64 intresult; Timestamp epoch; int tz; int type, val; char *lowunits; - double dummy; fsec_t fsec; struct pg_tm tt, *tm = &tt; @@ -4820,11 +4900,28 @@ timestamptz_part(PG_FUNCTION_ARGS) if (TIMESTAMP_NOT_FINITE(timestamp)) { - result = NonFiniteTimestampTzPart(type, val, lowunits, - TIMESTAMP_IS_NOBEGIN(timestamp), - true); - if (result) - PG_RETURN_FLOAT8(result); + double r = NonFiniteTimestampTzPart(type, val, lowunits, + TIMESTAMP_IS_NOBEGIN(timestamp), + true); + + if (r) + { + if (retnumeric) + { + if (r < 0) + return DirectFunctionCall3(numeric_in, + CStringGetDatum("-Infinity"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + else if (r > 0) + return DirectFunctionCall3(numeric_in, + CStringGetDatum("Infinity"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + } + else + PG_RETURN_FLOAT8(r); + } else PG_RETURN_NULL(); } @@ -4839,111 +4936,129 @@ timestamptz_part(PG_FUNCTION_ARGS) switch (val) { case DTK_TZ: - result = -tz; + intresult = -tz; break; case DTK_TZ_MINUTE: - result = -tz; - result /= SECS_PER_MINUTE; - FMODULO(result, dummy, (double) MINS_PER_HOUR); + intresult = (-tz / SECS_PER_MINUTE) % MINS_PER_HOUR; break; case DTK_TZ_HOUR: - dummy = -tz; - FMODULO(dummy, result, (double) SECS_PER_HOUR); + intresult = -tz / SECS_PER_HOUR; break; case DTK_MICROSEC: - result = tm->tm_sec * 1000000.0 + fsec; + intresult = tm->tm_sec * 1000000 + fsec; break; case DTK_MILLISEC: - result = tm->tm_sec * 1000.0 + fsec / 1000.0; + if (retnumeric) + /*--- + * tm->tm_sec * 1000 + fsec / 1000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 3)); + else + PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0); break; case DTK_SECOND: - result = tm->tm_sec + fsec / 1000000.0; + if (retnumeric) + /*--- + * tm->tm_sec + fsec / 1'000'000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 6)); + else + PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0); break; case DTK_MINUTE: - result = tm->tm_min; + intresult = tm->tm_min; break; case DTK_HOUR: - result = tm->tm_hour; + intresult = tm->tm_hour; break; case DTK_DAY: - result = tm->tm_mday; + intresult = tm->tm_mday; break; case DTK_MONTH: - result = tm->tm_mon; + intresult = tm->tm_mon; break; case DTK_QUARTER: - result = (tm->tm_mon - 1) / 3 + 1; + intresult = (tm->tm_mon - 1) / 3 + 1; break; case DTK_WEEK: - result = (float8) date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday); + intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday); break; case DTK_YEAR: if (tm->tm_year > 0) - result = tm->tm_year; + intresult = tm->tm_year; else /* there is no year 0, just 1 BC and 1 AD */ - result = tm->tm_year - 1; + intresult = tm->tm_year - 1; break; case DTK_DECADE: /* see comments in timestamp_part */ if (tm->tm_year > 0) - result = tm->tm_year / 10; + intresult = tm->tm_year / 10; else - result = -((8 - (tm->tm_year - 1)) / 10); + intresult = -((8 - (tm->tm_year - 1)) / 10); break; case DTK_CENTURY: /* see comments in timestamp_part */ if (tm->tm_year > 0) - result = (tm->tm_year + 99) / 100; + intresult = (tm->tm_year + 99) / 100; else - result = -((99 - (tm->tm_year - 1)) / 100); + intresult = -((99 - (tm->tm_year - 1)) / 100); break; case DTK_MILLENNIUM: /* see comments in timestamp_part */ if (tm->tm_year > 0) - result = (tm->tm_year + 999) / 1000; + intresult = (tm->tm_year + 999) / 1000; else - result = -((999 - (tm->tm_year - 1)) / 1000); + intresult = -((999 - (tm->tm_year - 1)) / 1000); break; case DTK_JULIAN: - result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday); - result += ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + - tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY; + if (retnumeric) + PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)), + numeric_div_opt_error(int64_to_numeric(((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * 1000000LL + fsec), + int64_to_numeric(SECS_PER_DAY * 1000000LL), + NULL), + NULL)); + else + PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + + ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + + tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY); break; case DTK_ISOYEAR: - result = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday); + intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday); /* Adjust BC years */ - if (result <= 0) - result -= 1; + if (intresult <= 0) + intresult -= 1; break; case DTK_DOW: case DTK_ISODOW: - result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)); - if (val == DTK_ISODOW && result == 0) - result = 7; + intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)); + if (val == DTK_ISODOW && intresult == 0) + intresult = 7; break; case DTK_DOY: - result = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - - date2j(tm->tm_year, 1, 1) + 1); + intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + - date2j(tm->tm_year, 1, 1) + 1); break; default: @@ -4951,7 +5066,7 @@ timestamptz_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("timestamp with time zone units \"%s\" not supported", lowunits))); - result = 0; + intresult = 0; } } @@ -4961,11 +5076,37 @@ timestamptz_part(PG_FUNCTION_ARGS) { case DTK_EPOCH: epoch = SetEpochTimestamp(); - /* try to avoid precision loss in subtraction */ - if (timestamp < (PG_INT64_MAX + epoch)) - result = (timestamp - epoch) / 1000000.0; + /* (timestamp - epoch) / 1000000 */ + if (retnumeric) + { + Numeric result; + + if (timestamp < (PG_INT64_MAX + epoch)) + result = int64_div_fast_to_numeric(timestamp - epoch, 6); + else + { + result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp), + int64_to_numeric(epoch), + NULL), + int64_to_numeric(1000000), + NULL); + result = DatumGetNumeric(DirectFunctionCall2(numeric_round, + NumericGetDatum(result), + Int32GetDatum(6))); + } + PG_RETURN_NUMERIC(result); + } else - result = ((float8) timestamp - epoch) / 1000000.0; + { + float8 result; + + /* try to avoid precision loss in subtraction */ + if (timestamp < (PG_INT64_MAX + epoch)) + result = (timestamp - epoch) / 1000000.0; + else + result = ((float8) timestamp - epoch) / 1000000.0; + PG_RETURN_FLOAT8(result); + } break; default: @@ -4973,7 +5114,7 @@ timestamptz_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("timestamp with time zone units \"%s\" not supported", lowunits))); - result = 0; + intresult = 0; } } else @@ -4983,22 +5124,37 @@ timestamptz_part(PG_FUNCTION_ARGS) errmsg("timestamp with time zone units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } - PG_RETURN_FLOAT8(result); + if (retnumeric) + PG_RETURN_NUMERIC(int64_to_numeric(intresult)); + else + PG_RETURN_FLOAT8(intresult); +} + +Datum +timestamptz_part(PG_FUNCTION_ARGS) +{ + return timestamptz_part_common(fcinfo, false); +} + +Datum +extract_timestamptz(PG_FUNCTION_ARGS) +{ + return timestamptz_part_common(fcinfo, true); } -/* interval_part() +/* interval_part() and extract_interval() * Extract specified field from interval. */ -Datum -interval_part(PG_FUNCTION_ARGS) +static Datum +interval_part_common(PG_FUNCTION_ARGS, bool retnumeric) { text *units = PG_GETARG_TEXT_PP(0); Interval *interval = PG_GETARG_INTERVAL_P(1); - float8 result; + int64 intresult; int type, val; char *lowunits; @@ -5021,54 +5177,68 @@ interval_part(PG_FUNCTION_ARGS) switch (val) { case DTK_MICROSEC: - result = tm->tm_sec * 1000000.0 + fsec; + intresult = tm->tm_sec * 1000000 + fsec; break; case DTK_MILLISEC: - result = tm->tm_sec * 1000.0 + fsec / 1000.0; + if (retnumeric) + /*--- + * tm->tm_sec * 1000 + fsec / 1000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 3)); + else + PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0); break; case DTK_SECOND: - result = tm->tm_sec + fsec / 1000000.0; + if (retnumeric) + /*--- + * tm->tm_sec + fsec / 1'000'000 + * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000 + */ + PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * 1000000LL + fsec, 6)); + else + PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0); break; case DTK_MINUTE: - result = tm->tm_min; + intresult = tm->tm_min; break; case DTK_HOUR: - result = tm->tm_hour; + intresult = tm->tm_hour; break; case DTK_DAY: - result = tm->tm_mday; + intresult = tm->tm_mday; break; case DTK_MONTH: - result = tm->tm_mon; + intresult = tm->tm_mon; break; case DTK_QUARTER: - result = (tm->tm_mon / 3) + 1; + intresult = (tm->tm_mon / 3) + 1; break; case DTK_YEAR: - result = tm->tm_year; + intresult = tm->tm_year; break; case DTK_DECADE: /* caution: C division may have negative remainder */ - result = tm->tm_year / 10; + intresult = tm->tm_year / 10; break; case DTK_CENTURY: /* caution: C division may have negative remainder */ - result = tm->tm_year / 100; + intresult = tm->tm_year / 100; break; case DTK_MILLENNIUM: /* caution: C division may have negative remainder */ - result = tm->tm_year / 1000; + intresult = tm->tm_year / 1000; break; default: @@ -5076,22 +5246,60 @@ interval_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("interval units \"%s\" not supported", lowunits))); - result = 0; + intresult = 0; } - } else { elog(ERROR, "could not convert interval to tm"); - result = 0; + intresult = 0; } } else if (type == RESERV && val == DTK_EPOCH) { - result = interval->time / 1000000.0; - result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR); - result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR); - result += ((double) SECS_PER_DAY) * interval->day; + if (retnumeric) + { + Numeric result; + int64 secs_from_day_month; + int64 val; + + /* this always fits into int64 */ + secs_from_day_month = ((int64) DAYS_PER_YEAR * (interval->month / MONTHS_PER_YEAR) + + (int64) DAYS_PER_MONTH * (interval->month % MONTHS_PER_YEAR) + + interval->day) * SECS_PER_DAY; + + /*--- + * result = secs_from_day_month + interval->time / 1'000'000 + * = (secs_from_day_month * 1'000'000 + interval->time) / 1'000'000 + */ + + /* + * Try the computation inside int64; if it overflows, do it in + * numeric (slower). This overflow happens around 10^9 days, so + * not common in practice. + */ + if (!pg_mul_s64_overflow(secs_from_day_month, 1000000, &val) && + !pg_add_s64_overflow(val, interval->time, &val)) + result = int64_div_fast_to_numeric(val, 6); + else + result = + numeric_add_opt_error(int64_div_fast_to_numeric(interval->time, 6), + int64_to_numeric(secs_from_day_month), + NULL); + + PG_RETURN_NUMERIC(result); + } + else + { + float8 result; + + result = interval->time / 1000000.0; + result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR); + result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR); + result += ((double) SECS_PER_DAY) * interval->day; + + PG_RETURN_FLOAT8(result); + } } else { @@ -5099,10 +5307,25 @@ interval_part(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("interval units \"%s\" not recognized", lowunits))); - result = 0; + intresult = 0; } - PG_RETURN_FLOAT8(result); + if (retnumeric) + PG_RETURN_NUMERIC(int64_to_numeric(intresult)); + else + PG_RETURN_FLOAT8(intresult); +} + +Datum +interval_part(PG_FUNCTION_ARGS) +{ + return interval_part_common(fcinfo, false); +} + +Datum +extract_interval(PG_FUNCTION_ARGS) +{ + return interval_part_common(fcinfo, true); } diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 613823c6ee8..4d534428d4e 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202104061 +#define CATALOG_VERSION_NO 202104062 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 73c22c8b4df..4309fa40dd2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -2339,9 +2339,15 @@ { oid => '1171', descr => 'extract field from timestamp with time zone', proname => 'date_part', provolatile => 's', prorettype => 'float8', proargtypes => 'text timestamptz', prosrc => 'timestamptz_part' }, +{ oid => '9983', descr => 'extract field from timestamp with time zone', + proname => 'extract', provolatile => 's', prorettype => 'numeric', + proargtypes => 'text timestamptz', prosrc => 'extract_timestamptz' }, { oid => '1172', descr => 'extract field from interval', proname => 'date_part', prorettype => 'float8', proargtypes => 'text interval', prosrc => 'interval_part' }, +{ oid => '9984', descr => 'extract field from interval', + proname => 'extract', prorettype => 'numeric', proargtypes => 'text interval', + prosrc => 'extract_interval' }, { oid => '1174', descr => 'convert date to timestamp with time zone', proname => 'timestamptz', provolatile => 's', prorettype => 'timestamptz', proargtypes => 'date', prosrc => 'date_timestamptz' }, @@ -2489,6 +2495,9 @@ { oid => '1273', descr => 'extract field from time with time zone', proname => 'date_part', prorettype => 'float8', proargtypes => 'text timetz', prosrc => 'timetz_part' }, +{ oid => '9981', descr => 'extract field from time with time zone', + proname => 'extract', prorettype => 'numeric', proargtypes => 'text timetz', + prosrc => 'extract_timetz' }, { oid => '1274', proname => 'int84pl', prorettype => 'int8', proargtypes => 'int8 int4', prosrc => 'int84pl' }, @@ -2834,9 +2843,15 @@ proname => 'date_part', prolang => 'sql', prorettype => 'float8', proargtypes => 'text date', prosrc => 'select pg_catalog.date_part($1, cast($2 as timestamp without time zone))' }, +{ oid => '9979', descr => 'extract field from date', + proname => 'extract', prorettype => 'numeric', proargtypes => 'text date', + prosrc => 'extract_date' }, { oid => '1385', descr => 'extract field from time', proname => 'date_part', prorettype => 'float8', proargtypes => 'text time', prosrc => 'time_part' }, +{ oid => '9980', descr => 'extract field from time', + proname => 'extract', prorettype => 'numeric', proargtypes => 'text time', + prosrc => 'extract_time' }, { oid => '1386', descr => 'date difference from today preserving months and years', proname => 'age', prolang => 'sql', provolatile => 's', @@ -5835,6 +5850,9 @@ { oid => '2021', descr => 'extract field from timestamp', proname => 'date_part', prorettype => 'float8', proargtypes => 'text timestamp', prosrc => 'timestamp_part' }, +{ oid => '9982', descr => 'extract field from timestamp', + proname => 'extract', prorettype => 'numeric', + proargtypes => 'text timestamp', prosrc => 'extract_timestamp' }, { oid => '2024', descr => 'convert date to timestamp', proname => 'timestamp', prorettype => 'timestamp', proargtypes => 'date', prosrc => 'date_timestamp' }, diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h index a362b5beb2b..dfc8688ca22 100644 --- a/src/include/utils/numeric.h +++ b/src/include/utils/numeric.h @@ -63,6 +63,7 @@ extern char *numeric_out_sci(Numeric num, int scale); extern char *numeric_normalize(Numeric num); extern Numeric int64_to_numeric(int64 val); +extern Numeric int64_div_fast_to_numeric(int64 val1, int log10val2); extern Numeric numeric_add_opt_error(Numeric num1, Numeric num2, bool *have_error); diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index 87fd2fbfd00..f50ef766857 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -1787,7 +1787,7 @@ select select pg_get_viewdef('tt201v', true); pg_get_viewdef ----------------------------------------------------------------------------------------------- - SELECT date_part('day'::text, now()) AS extr, + + SELECT EXTRACT(day FROM now()) AS extr, + ((now(), '@ 1 day'::interval) OVERLAPS (CURRENT_TIMESTAMP(2), '@ 1 day'::interval)) AS o,+ (('foo'::text) IS NORMALIZED) AS isn, + (('foo'::text) IS NFKC NORMALIZED) AS isnn, + diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index 83ad111f1c8..c8b0566ff40 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -966,66 +966,66 @@ SELECT f1 as "date", -- epoch -- SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0 - date_part ------------ - 0 + extract +--------- + 0 (1 row) -- -- century -- SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2 - date_part ------------ - -2 + extract +--------- + -2 (1 row) SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1 - date_part ------------ - -1 + extract +--------- + -1 (1 row) SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1 - date_part ------------ - -1 + extract +--------- + -1 (1 row) SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 - date_part ------------ - 1 + extract +--------- + 1 (1 row) SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 - date_part ------------ - 1 + extract +--------- + 1 (1 row) SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 - date_part ------------ - 19 + extract +--------- + 19 (1 row) SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 - date_part ------------ - 20 + extract +--------- + 20 (1 row) SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 - date_part ------------ - 20 + extract +--------- + 20 (1 row) SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 - date_part ------------ - 21 + extract +--------- + 21 (1 row) SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true @@ -1038,217 +1038,218 @@ SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true -- millennium -- SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 - date_part ------------ - -1 + extract +--------- + -1 (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 - date_part ------------ - 1 + extract +--------- + 1 (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 - date_part ------------ - 1 + extract +--------- + 1 (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 - date_part ------------ - 2 + extract +--------- + 2 (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 - date_part ------------ - 2 + extract +--------- + 2 (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 - date_part ------------ - 3 + extract +--------- + 3 (1 row) -- next test to be fixed on the turn of the next millennium;-) SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 - date_part ------------ - 3 + extract +--------- + 3 (1 row) -- -- decade -- SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199 - date_part ------------ - 199 + extract +--------- + 199 (1 row) SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1 - date_part ------------ - 1 + extract +--------- + 1 (1 row) SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0 - date_part ------------ - 0 + extract +--------- + 0 (1 row) SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); -- 0 - date_part ------------ - 0 + extract +--------- + 0 (1 row) SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); -- -1 - date_part ------------ - -1 + extract +--------- + -1 (1 row) SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); -- -1 - date_part ------------ - -1 + extract +--------- + -1 (1 row) SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2 - date_part ------------ - -2 + extract +--------- + -2 (1 row) -- -- all possible fields -- SELECT EXTRACT(MICROSECONDS FROM DATE '2020-08-11'); - date_part ------------ - 0 -(1 row) - +ERROR: date units "microseconds" not supported SELECT EXTRACT(MILLISECONDS FROM DATE '2020-08-11'); - date_part ------------ - 0 -(1 row) - +ERROR: date units "milliseconds" not supported SELECT EXTRACT(SECOND FROM DATE '2020-08-11'); - date_part ------------ - 0 -(1 row) - +ERROR: date units "second" not supported SELECT EXTRACT(MINUTE FROM DATE '2020-08-11'); - date_part ------------ - 0 -(1 row) - +ERROR: date units "minute" not supported SELECT EXTRACT(HOUR FROM DATE '2020-08-11'); - date_part ------------ - 0 -(1 row) - +ERROR: date units "hour" not supported SELECT EXTRACT(DAY FROM DATE '2020-08-11'); - date_part ------------ - 11 + extract +--------- + 11 (1 row) SELECT EXTRACT(MONTH FROM DATE '2020-08-11'); - date_part ------------ - 8 + extract +--------- + 8 (1 row) SELECT EXTRACT(YEAR FROM DATE '2020-08-11'); - date_part ------------ - 2020 + extract +--------- + 2020 +(1 row) + +SELECT EXTRACT(YEAR FROM DATE '2020-08-11 BC'); + extract +--------- + -2020 (1 row) SELECT EXTRACT(DECADE FROM DATE '2020-08-11'); - date_part ------------ - 202 + extract +--------- + 202 (1 row) SELECT EXTRACT(CENTURY FROM DATE '2020-08-11'); - date_part ------------ - 21 + extract +--------- + 21 (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE '2020-08-11'); - date_part ------------ - 3 + extract +--------- + 3 (1 row) SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11'); - date_part ------------ - 2020 + extract +--------- + 2020 +(1 row) + +SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11 BC'); + extract +--------- + -2020 (1 row) SELECT EXTRACT(QUARTER FROM DATE '2020-08-11'); - date_part ------------ - 3 + extract +--------- + 3 (1 row) SELECT EXTRACT(WEEK FROM DATE '2020-08-11'); - date_part ------------ - 33 + extract +--------- + 33 (1 row) SELECT EXTRACT(DOW FROM DATE '2020-08-11'); - date_part ------------ - 2 + extract +--------- + 2 +(1 row) + +SELECT EXTRACT(DOW FROM DATE '2020-08-16'); + extract +--------- + 0 (1 row) SELECT EXTRACT(ISODOW FROM DATE '2020-08-11'); - date_part ------------ - 2 + extract +--------- + 2 +(1 row) + +SELECT EXTRACT(ISODOW FROM DATE '2020-08-16'); + extract +--------- + 7 (1 row) SELECT EXTRACT(DOY FROM DATE '2020-08-11'); - date_part ------------ - 224 + extract +--------- + 224 (1 row) SELECT EXTRACT(TIMEZONE FROM DATE '2020-08-11'); -ERROR: timestamp units "timezone" not supported -CONTEXT: SQL function "date_part" statement 1 +ERROR: date units "timezone" not supported SELECT EXTRACT(TIMEZONE_M FROM DATE '2020-08-11'); -ERROR: timestamp units "timezone_m" not supported -CONTEXT: SQL function "date_part" statement 1 +ERROR: date units "timezone_m" not supported SELECT EXTRACT(TIMEZONE_H FROM DATE '2020-08-11'); -ERROR: timestamp units "timezone_h" not supported -CONTEXT: SQL function "date_part" statement 1 +ERROR: date units "timezone_h" not supported SELECT EXTRACT(EPOCH FROM DATE '2020-08-11'); - date_part + extract ------------ 1597104000 (1 row) SELECT EXTRACT(JULIAN FROM DATE '2020-08-11'); - date_part ------------ - 2459073 + extract +--------- + 2459073 (1 row) -- @@ -1344,173 +1345,124 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today' -- -- oscillating fields from non-finite date: -- -SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(HOUR FROM DATE '-infinity'); -- NULL - date_part ------------ - -(1 row) - --- all possible fields -SELECT EXTRACT(MICROSECONDS FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(MILLISECONDS FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(SECOND FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(MINUTE FROM DATE 'infinity'); -- NULL - date_part ------------ - +SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL + extract +--------- + (1 row) -SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL - date_part ------------ - +SELECT EXTRACT(DAY FROM DATE '-infinity'); -- NULL + extract +--------- + (1 row) +-- all supported fields SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) SELECT EXTRACT(MONTH FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) SELECT EXTRACT(QUARTER FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) SELECT EXTRACT(WEEK FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) SELECT EXTRACT(DOW FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) SELECT EXTRACT(ISODOW FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) SELECT EXTRACT(DOY FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(TIMEZONE FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(TIMEZONE_M FROM DATE 'infinity'); -- NULL - date_part ------------ - -(1 row) - -SELECT EXTRACT(TIMEZONE_H FROM DATE 'infinity'); -- NULL - date_part ------------ - + extract +--------- + (1 row) -- -- monotonic fields from non-finite date: -- SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(EPOCH FROM DATE '-infinity'); -- -Infinity - date_part + extract ----------- -Infinity (1 row) --- all possible fields +-- all supported fields SELECT EXTRACT(YEAR FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(DECADE FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(CENTURY FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(JULIAN FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(ISOYEAR FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity - date_part ------------ - Infinity + extract +---------- + Infinity (1 row) -- -- wrong fields from non-finite date: -- -SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- ERROR: timestamp units "microsec" not recognized -ERROR: timestamp units "microsec" not recognized -CONTEXT: SQL function "date_part" statement 1 +SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- error +ERROR: date units "microsec" not recognized -- test constructors select make_date(2013, 7, 15); make_date diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index c5ffa9f2cc1..01919491376 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -948,18 +948,18 @@ SELECT f1, EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM, EXTRACT(EPOCH FROM f1) AS EPOCH FROM INTERVAL_TBL; - f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch --------------------------------+-------------+-------------+--------+--------+------+-----+-------+---------+------+--------+---------+------------+------------ - @ 1 min | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60 - @ 5 hours | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000 - @ 10 days | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000 - @ 34 years | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400 - @ 3 mons | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000 - @ 14 secs ago | -14000000 | -14000 | -14 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14 - @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784 - @ 6 years | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600 - @ 5 mons | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000 - @ 5 mons 12 hours | 0 | 0 | 0 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200 + f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch +-------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+------------------- + @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000 + @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000 + @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000 + @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072224000.000000 + @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000 + @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000 + @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000 + @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189216000.000000 + @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000 + @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000 (10 rows) SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error @@ -967,50 +967,79 @@ ERROR: interval units "fortnight" not recognized SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error ERROR: interval units "timezone" not supported SELECT EXTRACT(DECADE FROM INTERVAL '100 y'); - date_part ------------ - 10 + extract +--------- + 10 (1 row) SELECT EXTRACT(DECADE FROM INTERVAL '99 y'); - date_part ------------ - 9 + extract +--------- + 9 (1 row) SELECT EXTRACT(DECADE FROM INTERVAL '-99 y'); - date_part ------------ - -9 + extract +--------- + -9 (1 row) SELECT EXTRACT(DECADE FROM INTERVAL '-100 y'); - date_part ------------ - -10 + extract +--------- + -10 (1 row) SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); - date_part ------------ - 1 + extract +--------- + 1 (1 row) SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); - date_part ------------ - 0 + extract +--------- + 0 (1 row) SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); - date_part ------------ - 0 + extract +--------- + 0 (1 row) SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); - date_part ------------ - -1 + extract +--------- + -1 +(1 row) + +-- date_part implementation is mostly the same as extract, so only +-- test a few cases for additional coverage. +SELECT f1, + date_part('microsecond', f1) AS microsecond, + date_part('millisecond', f1) AS millisecond, + date_part('second', f1) AS second, + date_part('epoch', f1) AS epoch + FROM INTERVAL_TBL; + f1 | microsecond | millisecond | second | epoch +-------------------------------+-------------+-------------+--------+------------ + @ 1 min | 0 | 0 | 0 | 60 + @ 5 hours | 0 | 0 | 0 | 18000 + @ 10 days | 0 | 0 | 0 | 864000 + @ 34 years | 0 | 0 | 0 | 1072958400 + @ 3 mons | 0 | 0 | 0 | 7776000 + @ 14 secs ago | -14000000 | -14000 | -14 | -14 + @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 93784 + @ 6 years | 0 | 0 | 0 | 189345600 + @ 5 mons | 0 | 0 | 0 | 12960000 + @ 5 mons 12 hours | 0 | 0 | 0 | 13003200 +(10 rows) + +-- internal overflow test case +SELECT extract(epoch from interval '1000000000 days'); + extract +----------------------- + 86400000000000.000000 (1 row) diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out index eae6fbd0512..e09e3310165 100644 --- a/src/test/regress/expected/psql_crosstab.out +++ b/src/test/regress/expected/psql_crosstab.out @@ -17,12 +17,12 @@ SELECT v, EXTRACT(year FROM d), count(*) FROM ctv_data GROUP BY 1, 2 ORDER BY 1, 2; - v | date_part | count -----+-----------+------- - v0 | 2014 | 2 - v0 | 2015 | 1 - v1 | 2015 | 3 - v2 | 2015 | 1 + v | extract | count +----+---------+------- + v0 | 2014 | 2 + v0 | 2015 | 1 + v1 | 2015 | 3 + v2 | 2015 | 1 (4 rows) -- basic usage with 3 columns diff --git a/src/test/regress/expected/time.out b/src/test/regress/expected/time.out index 5303cc0c947..39b409feca9 100644 --- a/src/test/regress/expected/time.out +++ b/src/test/regress/expected/time.out @@ -131,33 +131,33 @@ HINT: Could not choose a best candidate operator. You might need to add explici -- test EXTRACT -- SELECT EXTRACT(MICROSECOND FROM TIME '2020-05-26 13:30:25.575401'); - date_part ------------ - 25575401 + extract +---------- + 25575401 (1 row) SELECT EXTRACT(MILLISECOND FROM TIME '2020-05-26 13:30:25.575401'); - date_part + extract ----------- 25575.401 (1 row) SELECT EXTRACT(SECOND FROM TIME '2020-05-26 13:30:25.575401'); - date_part + extract ----------- 25.575401 (1 row) SELECT EXTRACT(MINUTE FROM TIME '2020-05-26 13:30:25.575401'); - date_part ------------ - 30 + extract +--------- + 30 (1 row) SELECT EXTRACT(HOUR FROM TIME '2020-05-26 13:30:25.575401'); - date_part ------------ - 13 + extract +--------- + 13 (1 row) SELECT EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401'); -- error @@ -167,6 +167,32 @@ ERROR: "time" units "fortnight" not recognized SELECT EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401'); -- error ERROR: "time" units "timezone" not recognized SELECT EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401'); + extract +-------------- + 48625.575401 +(1 row) + +-- date_part implementation is mostly the same as extract, so only +-- test a few cases for additional coverage. +SELECT date_part('microsecond', TIME '2020-05-26 13:30:25.575401'); + date_part +----------- + 25575401 +(1 row) + +SELECT date_part('millisecond', TIME '2020-05-26 13:30:25.575401'); + date_part +----------- + 25575.401 +(1 row) + +SELECT date_part('second', TIME '2020-05-26 13:30:25.575401'); + date_part +----------- + 25.575401 +(1 row) + +SELECT date_part('epoch', TIME '2020-05-26 13:30:25.575401'); date_part -------------- 48625.575401 diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index fff3cf08c17..690656dfb2d 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -1012,6 +1012,84 @@ SELECT d1 as "timestamp", Mon Jan 01 17:32:01 2001 | 200 | 21 | 3 | 2451912 | 978370321 (65 rows) +-- extract implementation is mostly the same as date_part, so only +-- test a few cases for additional coverage. +SELECT d1 as "timestamp", + extract(microseconds from d1) AS microseconds, + extract(milliseconds from d1) AS milliseconds, + extract(seconds from d1) AS seconds, + round(extract(julian from d1)) AS julian, + extract(epoch from d1) AS epoch + FROM TIMESTAMP_TBL; + timestamp | microseconds | milliseconds | seconds | julian | epoch +-----------------------------+--------------+--------------+-----------+-----------+--------------------- + -infinity | | | | -Infinity | -Infinity + infinity | | | | Infinity | Infinity + Thu Jan 01 00:00:00 1970 | 0 | 0.000 | 0.000000 | 2440588 | 0.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:02 1997 | 2000000 | 2000.000 | 2.000000 | 2450491 | 855595922.000000 + Mon Feb 10 17:32:01.4 1997 | 1400000 | 1400.000 | 1.400000 | 2450491 | 855595921.400000 + Mon Feb 10 17:32:01.5 1997 | 1500000 | 1500.000 | 1.500000 | 2450491 | 855595921.500000 + Mon Feb 10 17:32:01.6 1997 | 1600000 | 1600.000 | 1.600000 | 2450491 | 855595921.600000 + Thu Jan 02 00:00:00 1997 | 0 | 0.000 | 0.000000 | 2450451 | 852163200.000000 + Thu Jan 02 03:04:05 1997 | 5000000 | 5000.000 | 5.000000 | 2450451 | 852174245.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Tue Jun 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865963921.000000 + Sat Sep 22 18:19:20 2001 | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001182760.000000 + Wed Mar 15 08:14:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451619 | 953108041.000000 + Wed Mar 15 13:14:02 2000 | 2000000 | 2000.000 | 2.000000 | 2451620 | 953126042.000000 + Wed Mar 15 12:14:03 2000 | 3000000 | 3000.000 | 3.000000 | 2451620 | 953122443.000000 + Wed Mar 15 03:14:04 2000 | 4000000 | 4000.000 | 4.000000 | 2451619 | 953090044.000000 + Wed Mar 15 02:14:05 2000 | 5000000 | 5000.000 | 5.000000 | 2451619 | 953086445.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:00 1997 | 0 | 0.000 | 0.000000 | 2450491 | 855595920.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Tue Jun 10 18:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865967521.000000 + Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000 + Tue Feb 11 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450492 | 855682321.000000 + Wed Feb 12 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450493 | 855768721.000000 + Thu Feb 13 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450494 | 855855121.000000 + Fri Feb 14 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450495 | 855941521.000000 + Sat Feb 15 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450496 | 856027921.000000 + Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000 + Tue Feb 16 17:32:01 0097 BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192711279.000000 + Sat Feb 16 17:32:01 0097 | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102029679.000000 + Thu Feb 16 17:32:01 0597 | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323575279.000000 + Tue Feb 16 17:32:01 1097 | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545120879.000000 + Sat Feb 16 17:32:01 1697 | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610906479.000000 + Thu Feb 16 17:32:01 1797 | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455232879.000000 + Tue Feb 16 17:32:01 1897 | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299559279.000000 + Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000 + Sat Feb 16 17:32:01 2097 | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011874321.000000 + Wed Feb 28 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450143 | 825528721.000000 + Thu Feb 29 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450144 | 825615121.000000 + Fri Mar 01 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450145 | 825701521.000000 + Mon Dec 30 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450449 | 851967121.000000 + Tue Dec 31 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450450 | 852053521.000000 + Wed Jan 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450451 | 852139921.000000 + Fri Feb 28 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450509 | 857151121.000000 + Sat Mar 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450510 | 857237521.000000 + Tue Dec 30 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450814 | 883503121.000000 + Wed Dec 31 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450815 | 883589521.000000 + Fri Dec 31 17:32:01 1999 | 1000000 | 1000.000 | 1.000000 | 2451545 | 946661521.000000 + Sat Jan 01 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451546 | 946747921.000000 + Sun Dec 31 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451911 | 978283921.000000 + Mon Jan 01 17:32:01 2001 | 1000000 | 1000.000 | 1.000000 | 2451912 | 978370321.000000 +(65 rows) + -- value near upper bound uses special case in code SELECT date_part('epoch', '294270-01-01 00:00:00'::timestamp); date_part @@ -1019,6 +1097,19 @@ SELECT date_part('epoch', '294270-01-01 00:00:00'::timestamp); 9224097091200 (1 row) +SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp); + extract +---------------------- + 9224097091200.000000 +(1 row) + +-- another internal overflow test case +SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp); + extract +-------------------- + 95617584000.000000 +(1 row) + -- TO_CHAR() SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') FROM TIMESTAMP_TBL; diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index ff070080731..421ef2578ac 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1189,6 +1189,85 @@ SELECT d1 as timestamptz, Mon Jan 01 17:32:01 2001 PST | -28800 | -8 | 0 (66 rows) +-- extract implementation is mostly the same as date_part, so only +-- test a few cases for additional coverage. +SELECT d1 as "timestamp", + extract(microseconds from d1) AS microseconds, + extract(milliseconds from d1) AS milliseconds, + extract(seconds from d1) AS seconds, + round(extract(julian from d1)) AS julian, + extract(epoch from d1) AS epoch + FROM TIMESTAMPTZ_TBL; + timestamp | microseconds | milliseconds | seconds | julian | epoch +---------------------------------+--------------+--------------+-----------+-----------+--------------------- + -infinity | | | | -Infinity | -Infinity + infinity | | | | Infinity | Infinity + Wed Dec 31 16:00:00 1969 PST | 0 | 0.000 | 0.000000 | 2440588 | 0.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:02 1997 PST | 2000000 | 2000.000 | 2.000000 | 2450491 | 855624722.000000 + Mon Feb 10 17:32:01.4 1997 PST | 1400000 | 1400.000 | 1.400000 | 2450491 | 855624721.400000 + Mon Feb 10 17:32:01.5 1997 PST | 1500000 | 1500.000 | 1.500000 | 2450491 | 855624721.500000 + Mon Feb 10 17:32:01.6 1997 PST | 1600000 | 1600.000 | 1.600000 | 2450491 | 855624721.600000 + Thu Jan 02 00:00:00 1997 PST | 0 | 0.000 | 0.000000 | 2450451 | 852192000.000000 + Thu Jan 02 03:04:05 1997 PST | 5000000 | 5000.000 | 5.000000 | 2450451 | 852203045.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Tue Jun 10 17:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865989121.000000 + Sat Sep 22 18:19:20 2001 PDT | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001207960.000000 + Wed Mar 15 08:14:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451619 | 953136841.000000 + Wed Mar 15 04:14:02 2000 PST | 2000000 | 2000.000 | 2.000000 | 2451619 | 953122442.000000 + Wed Mar 15 02:14:03 2000 PST | 3000000 | 3000.000 | 3.000000 | 2451619 | 953115243.000000 + Wed Mar 15 03:14:04 2000 PST | 4000000 | 4000.000 | 4.000000 | 2451619 | 953118844.000000 + Wed Mar 15 01:14:05 2000 PST | 5000000 | 5000.000 | 5.000000 | 2451619 | 953111645.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:00 1997 PST | 0 | 0.000 | 0.000000 | 2450491 | 855624720.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000 + Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000 + Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000 + Mon Feb 10 14:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855613921.000000 + Thu Jul 10 14:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450641 | 868570321.000000 + Tue Jun 10 18:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865992721.000000 + Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000 + Tue Feb 11 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450492 | 855711121.000000 + Wed Feb 12 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450493 | 855797521.000000 + Thu Feb 13 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450494 | 855883921.000000 + Fri Feb 14 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450495 | 855970321.000000 + Sat Feb 15 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450496 | 856056721.000000 + Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000 + Tue Feb 16 17:32:01 0097 PST BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192682479.000000 + Sat Feb 16 17:32:01 0097 PST | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102000879.000000 + Thu Feb 16 17:32:01 0597 PST | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323546479.000000 + Tue Feb 16 17:32:01 1097 PST | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545092079.000000 + Sat Feb 16 17:32:01 1697 PST | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610877679.000000 + Thu Feb 16 17:32:01 1797 PST | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455204079.000000 + Tue Feb 16 17:32:01 1897 PST | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299530479.000000 + Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000 + Sat Feb 16 17:32:01 2097 PST | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011903121.000000 + Wed Feb 28 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450143 | 825557521.000000 + Thu Feb 29 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450144 | 825643921.000000 + Fri Mar 01 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450145 | 825730321.000000 + Mon Dec 30 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450449 | 851995921.000000 + Tue Dec 31 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450450 | 852082321.000000 + Wed Jan 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450451 | 852168721.000000 + Fri Feb 28 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450509 | 857179921.000000 + Sat Mar 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450510 | 857266321.000000 + Tue Dec 30 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450814 | 883531921.000000 + Wed Dec 31 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450815 | 883618321.000000 + Fri Dec 31 17:32:01 1999 PST | 1000000 | 1000.000 | 1.000000 | 2451545 | 946690321.000000 + Sat Jan 01 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451546 | 946776721.000000 + Sun Dec 31 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451911 | 978312721.000000 + Mon Jan 01 17:32:01 2001 PST | 1000000 | 1000.000 | 1.000000 | 2451912 | 978399121.000000 +(66 rows) + -- value near upper bound uses special case in code SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz); date_part @@ -1196,6 +1275,19 @@ SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz); 9224097091200 (1 row) +SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz); + extract +---------------------- + 9224097091200.000000 +(1 row) + +-- another internal overflow test case +SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz); + extract +-------------------- + 95617584000.000000 +(1 row) + -- TO_CHAR() SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') FROM TIMESTAMPTZ_TBL; diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.out index 0ae5b5204d6..f4960c0166d 100644 --- a/src/test/regress/expected/timetz.out +++ b/src/test/regress/expected/timetz.out @@ -148,33 +148,33 @@ HINT: No operator matches the given name and argument types. You might need to -- test EXTRACT -- SELECT EXTRACT(MICROSECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); - date_part ------------ - 25575401 + extract +---------- + 25575401 (1 row) SELECT EXTRACT(MILLISECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); - date_part + extract ----------- 25575.401 (1 row) SELECT EXTRACT(SECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); - date_part + extract ----------- 25.575401 (1 row) SELECT EXTRACT(MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); - date_part ------------ - 30 + extract +--------- + 30 (1 row) SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); - date_part ------------ - 13 + extract +--------- + 13 (1 row) SELECT EXTRACT(DAY FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); -- error @@ -182,24 +182,50 @@ ERROR: "time with time zone" units "day" not recognized SELECT EXTRACT(FORTNIGHT FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); -- error ERROR: "time with time zone" units "fortnight" not recognized SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); + extract +--------- + -16200 +(1 row) + +SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); + extract +--------- + -4 +(1 row) + +SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); + extract +--------- + -30 +(1 row) + +SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); + extract +-------------- + 63025.575401 +(1 row) + +-- date_part implementation is mostly the same as extract, so only +-- test a few cases for additional coverage. +SELECT date_part('microsecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); date_part ----------- - -16200 + 25575401 (1 row) -SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); +SELECT date_part('millisecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); date_part ----------- - -4 + 25575.401 (1 row) -SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); +SELECT date_part('second', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); date_part ----------- - -30 + 25.575401 (1 row) -SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); +SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); date_part -------------- 63025.575401 diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index 1cdd6bdca12..8f7435b767c 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -284,14 +284,18 @@ SELECT EXTRACT(HOUR FROM DATE '2020-08-11'); SELECT EXTRACT(DAY FROM DATE '2020-08-11'); SELECT EXTRACT(MONTH FROM DATE '2020-08-11'); SELECT EXTRACT(YEAR FROM DATE '2020-08-11'); +SELECT EXTRACT(YEAR FROM DATE '2020-08-11 BC'); SELECT EXTRACT(DECADE FROM DATE '2020-08-11'); SELECT EXTRACT(CENTURY FROM DATE '2020-08-11'); SELECT EXTRACT(MILLENNIUM FROM DATE '2020-08-11'); SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11'); +SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11 BC'); SELECT EXTRACT(QUARTER FROM DATE '2020-08-11'); SELECT EXTRACT(WEEK FROM DATE '2020-08-11'); SELECT EXTRACT(DOW FROM DATE '2020-08-11'); +SELECT EXTRACT(DOW FROM DATE '2020-08-16'); SELECT EXTRACT(ISODOW FROM DATE '2020-08-11'); +SELECT EXTRACT(ISODOW FROM DATE '2020-08-16'); SELECT EXTRACT(DOY FROM DATE '2020-08-11'); SELECT EXTRACT(TIMEZONE FROM DATE '2020-08-11'); SELECT EXTRACT(TIMEZONE_M FROM DATE '2020-08-11'); @@ -321,14 +325,9 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today' -- -- oscillating fields from non-finite date: -- -SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(HOUR FROM DATE '-infinity'); -- NULL --- all possible fields -SELECT EXTRACT(MICROSECONDS FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(MILLISECONDS FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(SECOND FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(MINUTE FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL +SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL +SELECT EXTRACT(DAY FROM DATE '-infinity'); -- NULL +-- all supported fields SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL SELECT EXTRACT(MONTH FROM DATE 'infinity'); -- NULL SELECT EXTRACT(QUARTER FROM DATE 'infinity'); -- NULL @@ -336,15 +335,12 @@ SELECT EXTRACT(WEEK FROM DATE 'infinity'); -- NULL SELECT EXTRACT(DOW FROM DATE 'infinity'); -- NULL SELECT EXTRACT(ISODOW FROM DATE 'infinity'); -- NULL SELECT EXTRACT(DOY FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(TIMEZONE FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(TIMEZONE_M FROM DATE 'infinity'); -- NULL -SELECT EXTRACT(TIMEZONE_H FROM DATE 'infinity'); -- NULL -- -- monotonic fields from non-finite date: -- SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity SELECT EXTRACT(EPOCH FROM DATE '-infinity'); -- -Infinity --- all possible fields +-- all supported fields SELECT EXTRACT(YEAR FROM DATE 'infinity'); -- Infinity SELECT EXTRACT(DECADE FROM DATE 'infinity'); -- Infinity SELECT EXTRACT(CENTURY FROM DATE 'infinity'); -- Infinity @@ -355,7 +351,7 @@ SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity -- -- wrong fields from non-finite date: -- -SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- ERROR: timestamp units "microsec" not recognized +SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- error -- test constructors select make_date(2013, 7, 15); diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index 11c1929bef5..6d532398bd6 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -343,3 +343,15 @@ SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); + +-- date_part implementation is mostly the same as extract, so only +-- test a few cases for additional coverage. +SELECT f1, + date_part('microsecond', f1) AS microsecond, + date_part('millisecond', f1) AS millisecond, + date_part('second', f1) AS second, + date_part('epoch', f1) AS epoch + FROM INTERVAL_TBL; + +-- internal overflow test case +SELECT extract(epoch from interval '1000000000 days'); diff --git a/src/test/regress/sql/time.sql b/src/test/regress/sql/time.sql index 688bd6b75ba..3637f28798b 100644 --- a/src/test/regress/sql/time.sql +++ b/src/test/regress/sql/time.sql @@ -63,3 +63,10 @@ SELECT EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401'); -- error SELECT EXTRACT(FORTNIGHT FROM TIME '2020-05-26 13:30:25.575401'); -- error SELECT EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401'); -- error SELECT EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401'); + +-- date_part implementation is mostly the same as extract, so only +-- test a few cases for additional coverage. +SELECT date_part('microsecond', TIME '2020-05-26 13:30:25.575401'); +SELECT date_part('millisecond', TIME '2020-05-26 13:30:25.575401'); +SELECT date_part('second', TIME '2020-05-26 13:30:25.575401'); +SELECT date_part('epoch', TIME '2020-05-26 13:30:25.575401'); diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index ed03d7c2187..c43a1f22688 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -261,8 +261,21 @@ SELECT d1 as "timestamp", date_part( 'epoch', d1) AS epoch FROM TIMESTAMP_TBL; +-- extract implementation is mostly the same as date_part, so only +-- test a few cases for additional coverage. +SELECT d1 as "timestamp", + extract(microseconds from d1) AS microseconds, + extract(milliseconds from d1) AS milliseconds, + extract(seconds from d1) AS seconds, + round(extract(julian from d1)) AS julian, + extract(epoch from d1) AS epoch + FROM TIMESTAMP_TBL; + -- value near upper bound uses special case in code SELECT date_part('epoch', '294270-01-01 00:00:00'::timestamp); +SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp); +-- another internal overflow test case +SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp); -- TO_CHAR() SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index fd1bf50a9c1..17ced99efca 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -275,8 +275,21 @@ SELECT d1 as timestamptz, date_part( 'timezone_minute', d1) AS timezone_minute FROM TIMESTAMPTZ_TBL; +-- extract implementation is mostly the same as date_part, so only +-- test a few cases for additional coverage. +SELECT d1 as "timestamp", + extract(microseconds from d1) AS microseconds, + extract(milliseconds from d1) AS milliseconds, + extract(seconds from d1) AS seconds, + round(extract(julian from d1)) AS julian, + extract(epoch from d1) AS epoch + FROM TIMESTAMPTZ_TBL; + -- value near upper bound uses special case in code SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz); +SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz); +-- another internal overflow test case +SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz); -- TO_CHAR() SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql index f39ea121bf7..7b70f4656c9 100644 --- a/src/test/regress/sql/timetz.sql +++ b/src/test/regress/sql/timetz.sql @@ -70,3 +70,10 @@ SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401- SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30'); SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); + +-- date_part implementation is mostly the same as extract, so only +-- test a few cases for additional coverage. +SELECT date_part('microsecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); +SELECT date_part('millisecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); +SELECT date_part('second', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); +SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); |