时区转换函数

时区转换函数 

功能：

把时区1的时间转换成时区2的时间

参数：

• arg1 -- 输入时间
• arg2 -- 时区1（也是arg1当前时间所在的时区）
• arg3 -- 时区2（要转换的时区的时间）  

要求：

1. 参数arg1类型可为timestamp
2. 24个时区（由1-24表示）

 

在 pg_proc.h 中添加函数定义

src/include/catalog/pg_proc.h

	DATA(insert OID = 6668 (  timezone_convert  PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1114 "1114 23 23" _null_ _null_ _null_ _null_ _null_ timezone_convert _null_ _null_ _null_ ));
	DESCR("timestamp convert.");

 在  src/backend/utils/adt/myfuncs.c 中实现函数

Datum 
	timezone_convert(PG_FUNCTION_ARGS)
	{
		Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
		int32 zone1 = PG_GETARG_INT32(1);
		int32 zone2 = PG_GETARG_INT32(2);

        Timestamp result = 0;

		if (!((1 <= zone1 && zone1 <= 24) && (1 <= zone2 && zone2 <= 24)))
		{
			ereport(ERROR,
				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
				errmsg("timestamp out of range.the parameter is 1..24")));
		}
		
		if (TIMESTAMP_NOT_FINITE(timestamp))
		{
			PG_RETURN_TIMESTAMP(timestamp);
		}

		/** 实现时区转换 **/

		PG_RETURN_TIMESTAMP(result);
	}

获取参数判断合法性

思路：

Timestamp timestamp = PG_GETARG_TIMESTAMP(0);

timestamp -> day; timestamp -> hour;

hour  = hour + zone2 - zone1;

hour >= 24

    hour -= 24;

    day += 1;

hour < 0

    hour += 24;

    day -= 1;

return timestamp;

src/include/pgtime.h 定义了相关结构体

struct pg_tm
{
	int			tm_sec;        
	int			tm_min;
	int			tm_hour;
	int			tm_mday;        /* 1..31 */
	int			tm_mon;			/* origin 0, not 1 */
	int			tm_year;		/* relative to 1900 */
	int			tm_wday;        /* 0..6 （0是周一）*/
	int			tm_yday;        /* 1..366 Julian date */
	int			tm_isdst;
	long int	tm_gmtoff;
	const char *tm_zone;
};

/src/include/utils/timestamp.h

定义了timestamp 和 pg_tm 的转换方法

extern int tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *dt);
extern int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm * tm, fsec_t *fsec, const char **tzn, pg_tz *attimezone);

timestamp2tm() 第一个参数是输入timestamp，第三个是输出pg_tm，第四个是输出的小数秒，其他几个参数与时区相关，第2,5个参数也是出参，最后一个设置NULL就可以，表示当前会话时区。

流程：

代码：

Datum 
timezone_convert(PG_FUNCTION_ARGS)
{
	Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
	int32 zone1 = PG_GETARG_INT32(1);
	int32 zone2 = PG_GETARG_INT32(2);
	struct pg_tm tt, *tm = &tt;
	int day;

	fsec_t     fsec;
	Timestamp result = 0;

	if (!((1 <= zone1 && zone1 <= 24) && (1 <= zone2 && zone2 <= 24)))
	{
		ereport(ERROR,
			(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
			errmsg("timestamp out of range.the parameter is 1..24")));
	}
	
	if (TIMESTAMP_NOT_FINITE(timestamp))
	{
		PG_RETURN_TIMESTAMP(timestamp);
	}
	if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
	{
		ereport(ERROR,
			(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
			errmsg("timestamp out of range")));
	}
	
	day = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
	tm->tm_hour = tm->tm_hour + zone2 - zone1;

	if(tm->tm_hour >= 24)
	{
		tm->tm_hour -= 24;
		day += 1;
	}
	else if(tm->tm_hour < 0)
	{
		tm->tm_hour += 24;
		day -= 1;
	}

	j2date(day, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);

	if (tm2timestamp(tm, fsec, NULL, &result) != 0)
	{
		ereport(ERROR,
			(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
			errmsg("timestamp out of range")));
	}

	PG_RETURN_TIMESTAMP(result);
}

 

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