日期转换星期函数

题目：

写个日期函数

要求：

1. 输入日期（输入类型可为 date，timestamp 和 timestamptz），结果返回星期几。

先了解一下这几种日期类型

postgres=# create table a (r_date date,r_timestamp timestamp,r_timestamptz timestamptz);
CREATE TABLE
postgres=# \d a
					Table "public.a"
	Column     |            Type             | Modifiers 
---------------+-----------------------------+-----------
 r_date        | date                        | 
 r_timestamp   | timestamp without time zone | 
 r_timestamptz | timestamp with time zone    | 

postgres=# insert into a values (now(),now(),now());
INSERT 0 1
postgres=# select * from a;
   r_date   |        r_timestamp         |         r_timestamptz         
------------+----------------------------+-------------------------------
 2017-03-26 | 2017-03-26 19:30:19.816182 | 2017-03-26 19:30:19.816182-07	//日期 精度 时区
(1 row)

 接下来开始添加自定义日期函数

步骤可参考：https://my.oschina.net/u/3389270/blog/869121

先看一下这几个日期类型的 OID

postgres=# select oid,typname from pg_type where typname = 'timestamp' or typname = 'timestamptz' or typname = 'date';
 oid  |   typname   
------+-------------
 1082 | date
 1114 | timestamp
 1184 | timestamptz
(3 rows)

思路：

首先添加函数定义，再添加实现函数，函数可正常获取参数及返回，添加日期转换星期几的功能

 

在 pg_proc.h 中添加函数定义

src/include/catalog/pg_proc.h

DATA(insert OID = 6664 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1082 "1082" _null_ _null_ _null_ _null_ _null_ date_to_week _null_ _null_ _null_ ));
DESCR("date_to_week.");
DATA(insert OID = 6665 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1114 "1114" _null_ _null_ _null_ _null_ _null_ timestamp_to_week _null_ _null_ _null_ ));
DESCR("timestamp_to_week.");
DATA(insert OID = 6666 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1184 "1184" _null_ _null_ _null_ _null_ _null_ timestamptz_to_week _null_ _null_ _null_ ));
DESCR("timestamptz_to_week.");

在 myfuncs.c 中添加实现函数

Datum date_to_week (PG_FUNCTION_ARGS)
{
	DateADT dateval = PG_GETARG_DATEADT(0);

	PG_RETURN_DATEADT(dateval);
}

Datum timestamp_to_week (PG_FUNCTION_ARGS)
{
	Timestamp timestampval = PG_GETARG_TIMESTAMP(0);

	PG_RETURN_TIMESTAMP(timestampval);
}

Datum timestamptz_to_week (PG_FUNCTION_ARGS)
{
	TimestampTz timestamptzval = PG_GETARG_TIMESTAMPTZ(0);

	PG_RETURN_TIMESTAMPTZ(timestamptzval);
}

以上函数只是获取日期，返回日期，函数可正常使用后可添加日期转星期的功能。

修改函数：

src/backend/utils/adt/datetime.c

提供了 date 转换星期几的函数

/*
 * j2day - convert Julian date to day-of-week (0..6 == Sun..Sat)
 *
 * Note: various places use the locution j2day(date - 1) to produce a
 * result according to the convention 0..6 = Mon..Sun.  This is a bit of
 * a crock, but will work as long as the computation here is just a modulo.
 */
int
j2day(int date)
{
	unsigned int day;

	day = date;

	day += 1;
	day %= 7;

	return (int) day;
}	/* j2day() */

注意：

1. 0..6 == Sun..Sat
2. places use the locution j2day(date - 1) to produce a result

修改 pg_proc.h 函数定义的返回值类型 OID

DATA(insert OID = 6664 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 "1082" _null_ _null_ _null_ _null_ _null_ date_to_week _null_ _null_ _null_ ));
DESCR("date_to_week.");

修改 date_to_week() 函数，实现日期转换星期几

Datum date_to_week (PG_FUNCTION_ARGS)
{
	DateADT dateval = PG_GETARG_DATEADT(0);
	int week = j2day(dateval - 1);

	PG_RETURN_INT32(week);
}

 date 类型转换星期几已经完成，接下来的思路 timestamp 和 timestamptz 类型转换 date 类型，在重复以上即可。

src/backend/utils/adt/date.c
提供了 timestamp_date() 和 timestamptz_date() 函数：

/* timestamp_date()
 * Convert timestamp to date data type.
 */
Datum
timestamp_date(PG_FUNCTION_ARGS)
{
	Timestamp	timestamp = PG_GETARG_TIMESTAMP(0);
	DateADT		result;
	struct pg_tm tt,
			   *tm = &tt;
	fsec_t		fsec;

	if (TIMESTAMP_IS_NOBEGIN(timestamp))
		DATE_NOBEGIN(result);
	else if (TIMESTAMP_IS_NOEND(timestamp))
		DATE_NOEND(result);
	else
	{
		if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
			ereport(ERROR,
					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
					 errmsg("timestamp out of range")));

		result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
	}

	PG_RETURN_DATEADT(result);
}

/* timestamptz_date()
 * Convert timestamp with time zone to date data type.
 */
Datum
timestamptz_date(PG_FUNCTION_ARGS)
{
	TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
	DateADT		result;
	struct pg_tm tt,
			   *tm = &tt;
	fsec_t		fsec;
	int			tz;

	if (TIMESTAMP_IS_NOBEGIN(timestamp))
		DATE_NOBEGIN(result);
	else if (TIMESTAMP_IS_NOEND(timestamp))
		DATE_NOEND(result);
	else
	{
		if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
			ereport(ERROR,
					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
					 errmsg("timestamp out of range")));

		result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
	}

	PG_RETURN_DATEADT(result);
}

现在有了 timestamp_date() 和 timestamptz_date() 函数，接下来只需要调用完成类型转换就可以了。

src/include/fmgr.h

定义了调用单行函数的方法

/* These macros allow the collation argument to be omitted (with a default of
 * InvalidOid, ie, no collation).  They exist mostly for backwards
 * compatibility of source code.
 */
#define DirectFunctionCall1(func, arg1) \
	DirectFunctionCall1Coll(func, InvalidOid, arg1)
#define DirectFunctionCall2(func, arg1, arg2) \
	DirectFunctionCall2Coll(func, InvalidOid, arg1, arg2)
#define DirectFunctionCall3(func, arg1, arg2, arg3) \
	DirectFunctionCall3Coll(func, InvalidOid, arg1, arg2, arg3)
#define DirectFunctionCall4(func, arg1, arg2, arg3, arg4) \
	DirectFunctionCall4Coll(func, InvalidOid, arg1, arg2, arg3, arg4)
#define DirectFunctionCall5(func, arg1, arg2, arg3, arg4, arg5) \
	DirectFunctionCall5Coll(func, InvalidOid, arg1, arg2, arg3, arg4, arg5)
#define DirectFunctionCall6(func, arg1, arg2, arg3, arg4, arg5, arg6) \
	DirectFunctionCall6Coll(func, InvalidOid, arg1, arg2, arg3, arg4, arg5, arg6)
#define DirectFunctionCall7(func, arg1, arg2, arg3, arg4, arg5, arg6, arg7) \
	DirectFunctionCall7Coll(func, InvalidOid, arg1, arg2, arg3, arg4, arg5, arg6, arg7)
#define DirectFunctionCall8(func, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8) \
	DirectFunctionCall8Coll(func, InvalidOid, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8)
#define DirectFunctionCall9(func, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9) \
	DirectFunctionCall9Coll(func, InvalidOid, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9)

函数名后面的数字代表几个参数

src/backend/utils/fmgr/fmgr.c

/*
 * These are for invocation of a specifically named function with a
 * directly-computed parameter list.  Note that neither arguments nor result
 * are allowed to be NULL.  Also, the function cannot be one that needs to
 * look at FmgrInfo, since there won't be any.
 */
Datum
DirectFunctionCall1Coll(PGFunction func, Oid collation, Datum arg1)
{
	FunctionCallInfoData fcinfo;
	Datum		result;

	InitFunctionCallInfoData(fcinfo, NULL, 1, collation, NULL, NULL);

	fcinfo.arg[0] = arg1;
	fcinfo.argnull[0] = false;

	result = (*func) (&fcinfo);

	/* Check for null result, since caller is clearly not expecting one */
	if (fcinfo.isnull)
		elog(ERROR, "function %p returned NULL", (void *) func);

	return result;
}

DirectFunctionCall1Coll()  和 timestamp_date() 返回值类型都是 Datum

而我们的想法是 timestamp 类型的参数 经过 DirectFunctionCall1Coll() 调用 timestamp_date() 转换为 date 类型，但这里返回的是 Datum，所以还需要将 Datum 转换为 date

src/include/utils/date.h

定义了 Datum 类型转换为 date 类型的方法

#define DatumGetDateADT(X)	  ((DateADT) DatumGetInt32(X))

修改 timestamp_to_week() 函数：

Datum timestamp_to_week (PG_FUNCTION_ARGS)
{
	Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
	DateADT		result;
	result = DatumGetDateADT(DirectFunctionCall1(timestamp_date,timestamp));
	int week = j2day(result - 1);

	PG_RETURN_INT32(week);
}

同理 timestamptz 类型的也一样

完整的功能代码：

/* pg_proc.h */

DATA(insert OID = 6664 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 "1082" _null_ _null_ _null_ _null_ _null_ date_to_week _null_ _null_ _null_ ));
DESCR("date_to_week.");
DATA(insert OID = 6665 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 "1114" _null_ _null_ _null_ _null_ _null_ timestamp_to_week _null_ _null_ _null_ ));
DESCR("timestamp_to_week.");
DATA(insert OID = 6666 (  date_to_week 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 "1184" _null_ _null_ _null_ _null_ _null_ timestamptz_to_week _null_ _null_ _null_ ));
DESCR("timestamptz_to_week.");

/* myfuns.c */

Datum date_to_week (PG_FUNCTION_ARGS)
{
	DateADT dateval = PG_GETARG_DATEADT(0);
	int week = j2day(dateval - 1);

	PG_RETURN_INT32(week);
}

Datum timestamp_to_week (PG_FUNCTION_ARGS)
{
	Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
	DateADT		result;
	result = DatumGetDateADT(DirectFunctionCall1(timestamp_date,timestamp));
	int week = j2day(result - 1);

	PG_RETURN_INT32(week);
}

Datum timestamptz_to_week (PG_FUNCTION_ARGS)
{
	TimestampTz timestamptz = PG_GETARG_TIMESTAMPTZ(0);
	DateADT		result;
	result = DatumGetDateADT(DirectFunctionCall1(timestamptz_date,timestamptz));
	int week = j2day(result - 1);

	PG_RETURN_INT32(week);
}

结果：

psql (9.5.6)
Type "help" for help.

postgres=# select date_to_week(timestamptz 'now()');
 date_to_week 
--------------
            2
(1 row)

postgres=# select date_to_week(timestamp 'now()');
 date_to_week 
--------------
            2
(1 row)

postgres=# select date_to_week(date 'now()');
 date_to_week 
--------------
            2
(1 row)

 

转载于:https://my.oschina.net/yonj1e/blog/869208