函数创建失败
为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【GBase版本】:
【操作系统】:
【CPU】:
【问题描述】*:函数如下:
create function public_test.datediff(start_date VARCHAR(50),end_date VARCHAR(50)) returns INT $$
from datetime import datetime
from datetime import timedelta
import time
def dateDuration24(start_date, end_date):
holidays2023 = ["20230101", "20230102", "20230107", "20230108", "20230114", "20230115", "20230121", "20230122",
"20230123", "20230124", "20230125", "20230126", "20230127", "20230204", "20230205", "20230211",
"20230212", "20230218", "20230219", "20230225", "20230226", "20230304", "20230305", "20230311",
"20230312", "20230318", "20230319", "20230325", "20230326", "20230401", "20230402", "20230405",
"20230408", "20230409", "20230415", "20230416", "20230422", "20230429", "20230430", "20230501",
"20230502", "20230503", "20230507", "20230513", "20230514", "20230520", "20230521", "20230527",
"20230528", "20230603", "20230604", "20230610", "20230611", "20230617", "20230618", "20230622",
"20230623", "20230624", "20230701", "20230702", "20230708", "20230709", "20230715", "20230716",
"20230722", "20230723", "20230729", "20230730", "20230805", "20230806", "20230812", "20230813",
"20230819", "20230820", "20230826", "20230827", "20230902", "20230903", "20230909", "20230910",
"20230916", "20230917", "20230923", "20230924", "20230929", "20230930", "20231001", "20231002",
"20231003", "20231004", "20231005", "20231006", "20231014", "20231015", "20231021", "20231022",
"20231028", "20231029", "20231104", "20231105", "20231111", "20231112", "20231118", "20231119",
"20231125", "20231126", "20231202", "20231203", "20231209", "20231210", "20231216", "20231217",
"20231223", "20231224", "20231230", "20231231"]
holidays2024 = ["20240101", "20240106", "20240107", "20240113", "20240114", "20240120", "20240121", "20240127",
"20240128", "20240203", "20240210", "20240211", "20240212", "20240213", "20240214", "20240215",
"20240216", "20240217", "20240224", "20240225", "20240302", "20240303", "20240309", "20240310",
"20240316", "20240317", "20240323", "20240324", "20240330", "20240331", "20240404", "20240405",
"20240406", "20240413", "20240414", "20240420", "20240421", "20240427", "20240501", "20240502",
"20240503", "20240504", "20240505", "20240512", "20240518", "20240519", "20240525", "20240526",
"20240601", "20240602", "20240608", "20240609", "20240610", "20240615", "20240616", "20240622",
"20240623", "20240629", "20240630", "20240706", "20240707", "20240713", "20240714", "20240720",
"20240721", "20240727", "20240728", "20240803", "20240804", "20240810", "20240811", "20240817",
"20240818", "20240824", "20240825", "20240831", "20240901", "20240907", "20240908", "20240915",
"20240916", "20240917", "20240921", "20240922", "20240928", "20241001", "20241002", "20241003",
"20241004", "20241005", "20241006", "20241007", "20241013", "20241019", "20241020", "20241026",
"20241027", "20241102", "20241103", "20241109", "20241110", "20241116", "20241117", "20241123",
"20241124", "20241130", "20241201", "20241207", "20241208", "20241214", "20241215", "20241221",
"20241222", "20241228", "20241229"]
# 24年12月假期需要根据25年元旦假日进行调整
holidays = holidays2023 + holidays2024
total_second = 0
start_date_str = start_date[0:8]
end_date_str = end_date[0:8]
# 保证结束时间大于开始时间
if end_date < start_date:
return 0
# 开始和结束在同一天
if start_date_str == end_date_str:
# 判断非节假日
if start_date_str not in holidays:
total_second = total_second + time.mktime(time.strptime(end_date, "%Y%m%d%H%M%S")) - time.mktime(time.strptime(start_date, "%Y%m%d%H%M%S"))
else:
# 第一天时长
if start_date_str not in holidays:
def dateLists():
days = []
end_date_str = end_date[0:8]
start_data_tt = start_date[0:8]
sDate = datetime.strptime(start_data_tt, "%Y%m%d").date()
next_date = sDate + timedelta(days=1)
while next_date.strftime("%Y%m%d") < end_date_str:
if next_date.strftime("%Y%m%d") not in holidays:
days.append(next_date.strftime("%Y%m%d"))
next_date = next_date + timedelta(days=1)
return days
# 开始和结束不在同一天
days = dateLists()
total_second = total_second + time.mktime(time.strptime(start_date_str + "235959", "%Y%m%d%H%M%S")) - time.mktime(time.strptime(start_date, "%Y%m%d%H%M%S")) + 1
# 中间天数时长
total_second = total_second + len(days) * 3600 * 24
# 最后一天时长
if end_date_str not in holidays:
total_second = total_second + time.mktime(time.strptime(end_date, "%Y%m%d%H%M%S")) - time.mktime( time.strptime(end_date_str + "000000", "%Y%m%d%H%M%S"))
return(int(total_second))
# 需求1 计算两个时间点之间的时长差值,一天按照24小时计
# print dateDuration24("20231101092000","20231106182000")
# print (dateDuration24('20231102182000','20231107092000'))
# return int(dateDuration24(start_date,end_date))
$$ LANGUAGE plpythonu;
执行 select public_test.datediff('20231101092000','20231106182000');
结果为NULL,python脚本有结果
热门帖子
- 12025-12-01浏览数:182763
- 22023-05-09浏览数:25059
- 42023-09-25浏览数:18525
- 52020-05-11浏览数:17529

