Hello
this patch try to complete a set of functions make_date and make_timestamp.
Regards
Pavel
commit a1344a0624f87438e2a12c0a7263a0e6dc9a1a30
Author: Pavel Stehule <pavel.steh...@gooddata.com>
Date: Thu Dec 12 18:08:47 2013 +0100
initial implementation make_timestamp
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a411e3a..bdf285c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6735,6 +6735,30 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
<row>
<entry>
<indexterm>
+ <primary>make_timestamp</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_timestamp(<parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>,
+ <parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>timestamp</type></entry>
+ <entry>
+ Create timestamp from year, month, day, hour, minute and seconds fields
+ </entry>
+ <entry><literal>make_timestamp(1-23, 7, 15, 8, 15, 23.5)</literal></entry>
+ <entry><literal>2013-07-15 08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index c3c71b7..9faa5d9 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -477,6 +477,88 @@ timestamptz_in(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(result);
}
+/*
+ * make_timestamp() - timestamp constructor
+ */
+Datum
+make_timestamp(PG_FUNCTION_ARGS)
+{
+ struct pg_tm tm;
+ int tm_hour = PG_GETARG_INT32(3);
+ int tm_min = PG_GETARG_INT32(4);
+ double sec = PG_GETARG_FLOAT8(5);
+ TimeOffset date;
+ TimeOffset time;
+ int dterr;
+ Timestamp result;
+
+ tm.tm_year = PG_GETARG_INT32(0);
+ tm.tm_mon = PG_GETARG_INT32(1);
+ tm.tm_mday = PG_GETARG_INT32(2);
+
+ /*
+ * Note: we'll reject zero or negative year values. Perhaps negatives
+ * should be allowed to represent BC years?
+ */
+ dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);
+
+ if (dterr != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("date field value out of range: %d-%02d-%02d",
+ tm.tm_year, tm.tm_mon, tm.tm_mday)));
+
+ if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range: %d-%02d-%02d",
+ tm.tm_year, tm.tm_mon, tm.tm_mday)));
+
+ date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
+
+ /* This should match the checks in DecodeTimeOnly */
+ if (tm_hour < 0 || tm_min < 0 || tm_min > MINS_PER_HOUR - 1 ||
+ sec < 0 || sec > SECS_PER_MINUTE ||
+ tm_hour > HOURS_PER_DAY ||
+ /* test for > 24:00:00 */
+ (tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("time field value out of range: %d:%02d:%02g",
+ tm_hour, tm_min, sec)));
+
+ /* This should match tm2time */
+#ifdef HAVE_INT64_TIMESTAMP
+ time = (((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE)
+ * USECS_PER_SEC) + rint(sec * USECS_PER_SEC);
+
+ result = date * USECS_PER_DAY + time;
+ /* check for major overflow */
+ if ((result - time) / USECS_PER_DAY != date)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
+ tm.tm_year, tm.tm_mon, tm.tm_mday,
+ tm_hour, tm_min, sec)));
+
+ /* check for just-barely overflow (okay except time-of-day wraps) */
+ /* caution: we want to allow 1999-12-31 24:00:00 */
+ if ((result < 0 && date > 0) ||
+ (result > 0 && date < -1))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
+ tm.tm_year, tm.tm_mon, tm.tm_mday,
+ tm_hour, tm_min, sec)));
+#else
+ time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec;
+ result = date * SECS_PER_DAY + time;
+#endif
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
+
/* timestamptz_out()
* Convert a timestamp to external form.
*/
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0117500..6334fef 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4680,6 +4680,8 @@ DATA(insert OID = 3846 ( make_date PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1082
DESCR("construct date");
DATA(insert OID = 3847 ( make_time PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1083 "23 23 701" _null_ _null_ "{hour,min,sec}" _null_ make_time _null_ _null_ _null_ ));
DESCR("construct time");
+DATA(insert OID = 3920 ( make_timestamp PGNSP PGUID 12 1 0 0 0 f f f f t f i 6 0 1114 "23 23 23 23 23 701" _null_ _null_ "{year,month,day,hour,min,sec}" _null_ make_timestamp _null_ _null_ _null_ ));
+DESCR("construct timestamp");
/* spgist support functions */
DATA(insert OID = 4001 ( spggettuple PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 16 "2281 2281" _null_ _null_ _null_ _null_ spggettuple _null_ _null_ _null_ ));
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index e7a53ff..57862fa 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -121,6 +121,8 @@ extern Datum timestamp_gt_timestamptz(PG_FUNCTION_ARGS);
extern Datum timestamp_ge_timestamptz(PG_FUNCTION_ARGS);
extern Datum timestamp_cmp_timestamptz(PG_FUNCTION_ARGS);
+extern Datum make_timestamp(PG_FUNCTION_ARGS);
+
extern Datum timestamptz_eq_timestamp(PG_FUNCTION_ARGS);
extern Datum timestamptz_ne_timestamp(PG_FUNCTION_ARGS);
extern Datum timestamptz_lt_timestamp(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index db2cfe6..a092fc2 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1585,3 +1585,10 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 2001 1 1 1 1 1 1
(65 rows)
+-- timestamp numeric fields constructor
+SELECT make_timestamp(2014,12,28,6,30,45.887);
+ make_timestamp
+------------------------------
+ Sun Dec 28 06:30:45.887 2014
+(1 row)
+
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index c4ed4ee..b22cd48 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -222,3 +222,6 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMP_TBL;
+
+-- timestamp numeric fields constructor
+SELECT make_timestamp(2014,12,28,6,30,45.887);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
