Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf901bb5

Browse files
committed
Add make_date() and make_time() functions.
Pavel Stehule, reviewed by Jeevan Chalke and Atri Sharma
1 parent69c8fba commitf901bb5

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+
structpg_tmtm;
245+
DateADTdate;
246+
intdterr;
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+
inttm_hour=PG_GETARG_INT32(0);
1255+
inttm_min=PG_GETARG_INT32(1);
1256+
doublesec=PG_GETARG_FLOAT8(2);
1257+
TimeADTtime;
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+
#ifdefHAVE_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 intDecodeTimezone(char *str, int *tzp);
4444
staticconstdatetkn*datebsearch(constchar*key,constdatetkn*base,intnel);
4545
staticintDecodeDate(char*str,intfmask,int*tmask,bool*is2digits,
4646
structpg_tm*tm);
47-
staticintValidateDate(intfmask,boolisjulian,boolis2digits,boolbc,
48-
structpg_tm*tm);
4947
staticvoidTrimTrailingZeros(char*str);
5048
staticvoidAppendSeconds(char*cp,intsec,fsec_tfsec,
5149
intprecision,boolfillzeros);
@@ -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-
staticint
2271+
int
22742272
ValidateDate(intfmask,boolisjulian,boolis2digits,boolbc,
22752273
structpg_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-
#defineCATALOG_VERSION_NO201311162
56+
#defineCATALOG_VERSION_NO201311171
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(insertOID=3946 (int8rangePGNSPPGUID121000ffffffi303926"20 20 25"_null__null__null__null_range_constructor3_null__null__null_ ));
46744674
DESCR("int8range constructor");
46754675

4676+
/* date, time constructors */
4677+
DATA(insertOID=3846 (make_datePGNSPPGUID121000fffftfi301082"23 23 23"_null__null_"{year,month,day}"_null_make_date_null__null__null_ ));
4678+
DESCR("construct date");
4679+
DATA(insertOID=3847 (make_timePGNSPPGUID121000fffftfi301083"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(insertOID=4001 (spggettuplePGNSPPGUID121000fffftfv2016"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
externDatumdate_out(PG_FUNCTION_ARGS);
9898
externDatumdate_recv(PG_FUNCTION_ARGS);
9999
externDatumdate_send(PG_FUNCTION_ARGS);
100+
externDatummake_date(PG_FUNCTION_ARGS);
100101
externDatumdate_eq(PG_FUNCTION_ARGS);
101102
externDatumdate_ne(PG_FUNCTION_ARGS);
102103
externDatumdate_lt(PG_FUNCTION_ARGS);
@@ -154,6 +155,7 @@ extern Datum time_recv(PG_FUNCTION_ARGS);
154155
externDatumtime_send(PG_FUNCTION_ARGS);
155156
externDatumtimetypmodin(PG_FUNCTION_ARGS);
156157
externDatumtimetypmodout(PG_FUNCTION_ARGS);
158+
externDatummake_time(PG_FUNCTION_ARGS);
157159
externDatumtime_transform(PG_FUNCTION_ARGS);
158160
externDatumtime_scale(PG_FUNCTION_ARGS);
159161
externDatumtime_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
externvoidEncodeDateTime(structpg_tm*tm,fsec_tfsec,boolprint_tz,inttz,constchar*tzn,intstyle,char*str);
295295
externvoidEncodeInterval(structpg_tm*tm,fsec_tfsec,intstyle,char*str);
296296

297+
externintValidateDate(intfmask,boolisjulian,boolis2digits,boolbc,
298+
structpg_tm*tm);
299+
297300
externintDecodeSpecial(intfield,char*lowtoken,int*val);
298301
externintDecodeUnits(intfield,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'::dateas t;
277277
select'-infinity'::date<'today'::dateas 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)
[8]ページ先頭
©2009-2025 Movatter.jp