参考资料
Julian day number
pg source cdoe “src\backend\utils\adt\datetime.c”(317,1)
PostgreSQL Timestamp通过C函数转换为Date
Datum to_date1(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);
}
Datum to_date2(PG_FUNCTION_ARGS) {
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(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);
}
timestamp2tm(timestamp dt, int *tzp, struct tm *tm, fsec_t *fsec, const char **tzn),函数实现文件为”\src\interfaces\ecpg\pgtypeslib\timestamp.c”(99,1):\
其中调用了函数
int j2date(int jd, int *year, int *month, int *day),实现文件为”src\backend\utils\adt\datetime.c”(317,1);
int date2j(int y, int m, int d),实现文件为”src\backend\utils\adt\datetime.c”(291,1);
其中一些常量不是太明白,经过搜索,请参看文档
Julian day number
select make_date(2000,1,1) - make_interval(days=>2451545),
make_date(1970,1,1) - make_interval(days=>2440588 )
output
4714-11-24 00:00:00 BC, 4714-11-24 00:00:00 BC
因此儒略历的日期偏移为
/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
例如:2000年1月1日中午对应于JD = 2451545.0
drop function if exists d2j(date);
create or replace function d2j(date)
returns float8
as $$
declare
v_year integer;v_month integer;v_day integer;
v_a integer; v_y integer;v_m integer;
v_jdn integer;v_jd float8;
begin
v_year := date_part('year',$1);
v_month := date_part('month',$1);
v_day := date_part('day',$1);
v_a := (14-v_month) / 12;
v_y := v_year+4800-v_a;
v_m := v_month+12*v_a-3;
v_jdn := v_day + ((153 * v_m + 2)/5) + (365* v_y) + (v_y / 4) - (v_y / 100) + (v_y / 400) - 32045;
v_jd := v_jdn+ ( (0-12)/24 ) + (0/1440) + (0/86400);
return v_jd;
end;
$$ language plpgsql;
--验证结果,可以通过"jd mon 7"来计算星期几,其中0表示星期一。
with cte as(
select d2j(make_date(2000,1,1)) as f1
)select f1, (((f1::integer)%7) +1) as week from cte;