8000 Add make_date() and make_time() functions. · imace/postgres@f901bb5 · GitHub
[go: up one dir, main page]
Skip to content

Commit f901bb5

Browse files
tglsfdctglsfdc
committed
Add make_date() and make_time() functions.
Pavel Stehule, reviewed by Jeevan Chalke and Atri Sharma
1 parent 69c8fba commit f901bb5

File tree

9 files changed

+162
-4
lines changed

9 files changed

+162
-4
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6690,6 +6690,48 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
66906690
<entry></entry>
66916691
</row>
66926692

6693+
<row>
6694+
<entry>
6695+
<indexterm>
6696+
<primary>make_date</primary>
6697+
</indexterm>
6698+
<literal>
6699+
<function>
6700+
make_date(<parameter>year</parameter> <type>int</type>,
6701+
<parameter>month</parameter> <type>int</type>,
6702+
<parameter>day</parameter> <type>int</type>)
6703+
</function>
6704+
</literal>
6705+
</entry>
6706+
<entry><type>date</type></entry>
6707+
<entry>
6708+
Create date from year, month and day fields
6709+
</entry>
6710+
<entry><literal>make_date(2013, 7, 15)</literal></entry>
6711+
<entry><literal>2013-07-15</literal></entry>
6712+
</row>
6713+
6714+
<row>
6715+
<entry>
6716+
<indexterm>
6717+
<primary>make_time</primary>
6718+
</indexterm>
6719+
<literal>
6720+
<function>
6721+
make_time(<parameter>hour</parameter> <type>int</type>,
6722+
<parameter>min</parameter> <type>int</type>,
6723+
<parameter>sec</parameter> <type>double precision</type>)
6724+
</function>
6725+
</literal>
6726+
</entry>
6727+
<entry><type>time</type></entry>
6728+
<entry>
6729+
Create time from hour, minute and seconds fields
6730+
</entry>
6731+
<entry><literal>make_time(8, 15, 23.5)</literal></entry>
6732+
<entry><literal>08:15:23.5</literal></entry>
6733+
</row>
6734+
66936735
<row>
66946736
<entry>
66956737
<indexterm>

‎src/backend/utils/adt/date.c

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -235,6 +235,43 @@ date_send(PG_FUNCTION_ARGS)
235235
PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
236236
}
237237

238+
/*
239+
* make_date - date constructor
240+
*/
241+
Datum
242+
make_date(PG_FUNCTION_ARGS)
243+
{
244+
struct pg_tm tm;
245+
DateADT date;
246+
int dterr;
247+
248+
tm.tm_year = PG_GETARG_INT32(0);
249+
tm.tm_mon = PG_GETARG_INT32(1);
250+
tm.tm_mday = PG_GETARG_INT32(2);
251+
252+
/*
253+
* Note: we'll reject zero or negative year values. Perhaps negatives
254+
* should be allowed to represent BC years?
255+
*/
256+
dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);
257+
258+
if (dterr != 0)
259+
ereport(ERROR,
260+
(errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
261+
errmsg("date field value out of range: %d-%02d-%02d",
262+
tm.tm_year, tm.tm_mon, tm.tm_mday)));
263+
264+
if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
265+
ereport(ERROR,
266+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
267+
errmsg("date out of range: %d-%02d-%02d",
268+
tm.tm_year, tm.tm_mon, tm.tm_mday)));
269+
270+
date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
271+
272+
PG_RETURN_DATEADT(date);
273+
}
274+
238275
/*
239276
* Convert reserved date values to string.
240277
*/
@@ -1208,6 +1245,39 @@ timetypmodout(PG_FUNCTION_ARGS)
12081245
PG_RETURN_CSTRING(anytime_typmodout(false, typmod));
12091246
}
12101247

1248+
/*
1249+
* make_time - time constructor
1250+
*/
1251+
Datum
1252+
make_time(PG_FUNCTION_ARGS)
1253+
{
1254+
int tm_hour = PG_GETARG_INT32(0);
1255+
int tm_min = PG_GETARG_INT32(1);
1256+
double sec = PG_GETARG_FLOAT8(2);
1257+
TimeADT time;
1258+
1259+
/* This should match the checks in DecodeTimeOnly */
1260+
if (tm_hour < 0 || tm_min < 0 || tm_min > MINS_PER_HOUR - 1 ||
1261+
sec < 0 || sec > SECS_PER_MINUTE ||
1262+
tm_hour > HOURS_PER_DAY ||
1263+
/* test for > 24:00:00 */
1264+
(tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0)))
1265+
ereport(ERROR,
1266+
(errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
1267+
errmsg("time field value out of range: %d:%02d:%02g",
1268+
tm_hour, tm_min, sec)));
1269+
1270+
/* This should match tm2time */
1271+
#ifdef HAVE_INT64_TIMESTAMP
1272+
time = (((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE)
1273+
* USECS_PER_SEC) + rint(sec * USECS_PER_SEC);
1274+
#else
1275+
time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec;
1276+
#endif
1277+
1278+
PG_RETURN_TIMEADT(time);
1279+
}
1280+
12111281

12121282
/* time_transform()
12131283
* Flatten calls to time_scale() and timetz_scale() that solely represent

‎src/backend/utils/adt/datetime.c

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -44,8 +44,6 @@ static int DecodeTimezone(char *str, int *tzp);
4444
static const datetkn *datebsearch(const char *key, const datetkn *base, int nel);
4545
static int DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
4646
struct pg_tm * tm);
47-
static int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
48-
struct pg_tm * tm);
4947
static void TrimTrailingZeros(char *str);
5048
static void AppendSeconds(char *cp, int sec, fsec_t fsec,
5149
int precision, bool fillzeros);
@@ -2270,7 +2268,7 @@ DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
22702268
* Check valid year/month/day values, handle BC and DOY cases
22712269
* Return 0 if okay, a DTERR code if not.
22722270
*/
2273-
static int
2271+
int
22742272
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
22752273
struct pg_tm * tm)
22762274
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201311162
56+
#define CATALOG_VERSION_NO 201311171
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4673,6 +4673,12 @@ DESCR("int8range constructor");
46734673
DATA(insert OID = 3946 ( int8range PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 3926 "20 20 25" _null_ _null_ _null_ _null_ range_constructor3 _null_ _null_ _null_ ));
46744674
DESCR("int8range constructor");
46754675

4676+
/* date, time constructors */
4677+
DATA(insert OID = 3846 ( make_date PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1082 "23 23 23" _null_ _null_ "{year,month,day}" _null_ make_date _null_ _null_ _null_ ));
4678+
DESCR("construct date");
4679+
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_ ));
4680+
DESCR("construct time");
4681+
46764682
/* spgist support functions */
46774683
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_ ));
46784684
DESCR("spgist(internal)");

‎src/include/utils/date.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,6 +97,7 @@ extern Datum date_in(PG_FUNCTION_ARGS);
9797
extern Datum date_out(PG_FUNCTION_ARGS);
9898
extern Datum date_recv(PG_FUNCTION_ARGS);
9999
extern Datum date_send(PG_FUNCTION_ARGS);
100+
extern Datum make_date(PG_FUNCTION_ARGS);
100101
extern Datum date_eq(PG_FUNCTION_ARGS);
101102
extern Datum date_ne(PG_FUNCTION_ARGS);
102103
extern Datum date_lt(PG_FUNCTION_ARGS);
@@ -154,6 +155,7 @@ extern Datum time_recv(PG_FUNCTION_ARGS);
154155
extern Datum time_send(PG_FUNCTION_ARGS);
155156
extern Datum timetypmodin(PG_FUNCTION_ARGS);
156157
extern Datum timetypmodout(PG_FUNCTION_ARGS);
158+
extern Datum make_time(PG_FUNCTION_ARGS);
157159
extern Datum time_transform(PG_FUNCTION_ARGS);
158160
extern Datum time_scale(PG_FUNCTION_ARGS);
159161
extern Datum time_eq(PG_FUNCTION_ARGS);

‎src/include/utils/datetime.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -294,6 +294,9 @@ extern void EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, bool print_tz, int tz
294294
extern void EncodeDateTime(struct pg_tm * tm, fsec_t fsec, bool print_tz, int tz, const char *tzn, int style, char *str);
295295
extern void EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str);
296296

297+
extern int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
298+
struct pg_tm * tm);
299+
297300
extern int DecodeSpecial(int field, char *lowtoken, int *val);
298301
extern int DecodeUnits(int field, char *lowtoken, int *val);
299302

‎src/test/regress/expected/date.out

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1184,3 +1184,29 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
11841184
f | f | t
11851185
(1 row)
11861186

1187+
-- test constructors
1188+
select make_date(2013, 7, 15);
1189+
make_date
1190+
------------
1191+
07-15-2013
1192+
(1 row)
1193+
1194+
select make_time(8, 20, 0.0);
1195+
make_time
1196+
-----------
1197+
08:20:00
1198+
(1 row)
1199+
1200+
-- should fail
1201+
select make_date(2013, 2, 30);
1202+
ERROR: date field value out of range: 2013-02-30
1203+
select make_date(2013, 13, 1);
1204+
ERROR: date field value out of range: 2013-13-01
1205+
select make_date(2013, 11, -1);
1206+
ERROR: date field value out of range: 2013-11--1
1207+
select make_date(-44, 3, 15); -- perhaps we should allow this sometime?
1208+
ERROR: date field value out of range: -44-03-15
1209+
select make_time(10, 55, 100.1);
1210+
ERROR: time field value out of range: 10:55:100.1
1211+
select make_time(24, 0, 2.1);
1212+
ERROR: time field value out of range: 24:00:2.1

‎src/test/regress/sql/date.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -276,3 +276,14 @@ select 'infinity'::date, '-infinity'::date;
276276
select 'infinity'::date > 'today'::date as t;
277277
select '-infinity'::date < 'today'::date as t;
278278
select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date);
279+
280+
-- test constructors
281+
select make_date(2013, 7, 15);
282+
select make_time(8, 20, 0.0);
283+
-- should fail
284+
select make_date(2013, 2, 30);
285+
select make_date(2013, 13, 1);
286+
select make_date(2013, 11, -1);
287+
select make_date(-44, 3, 15); -- perhaps we should allow this sometime?
288+
select make_time(10, 55, 100.1);
289+
select make_time(24, 0, 2.1);

0 commit comments

Comments
 (0)
0