Phoenix查询日期相关函数

时间戳转日期(带时区)

-- 数据库optime字段为int类型的时间戳，这里要多一次。
to_char(CONVERT_TZ(to_date(CAST(to_char("optime",'#') AS varchar),'s'), 'GMT', 'Etc/GMT-9'),'yyyy-MM-dd HH:mm:ss')

-- 直接写死时间戳测试，少转一次类型
to_char(CONVERT_TZ(to_date('1582992000','s'), 'UTC', 'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss')

日期转时间戳(带时区)

select to_char(to_number(to_date('2020-03-01','yyyy-MM-dd','GMT+8'))/1000,'##########');

分区求值 使用last_value函数达到partition by

需求:统计货币表中，每个玩家获取钻石总和，消耗总和，以及最后剩余钻石数。
表结构：

SELECT DISTINCT a."rid" AS "rid",a."sid" AS "sid" ,a."rname" AS "rname",TOTAL_GET,TOTAL_COST,REMAIN FROM "a8_kr"."t_log_money" AS a  
     LEFT JOIN 
     (
        SELECT
	    "rid", sum(CASE WHEN "val">0 THEN "val" ELSE 0 end)  AS total_get,sum(CASE WHEN "val"<0 THEN "val" ELSE 0 end)  AS total_cost,LAST_VALUE("new") WITHIN GROUP (ORDER BY "optime" ASC) AS remain
        FROM
	    "a8_kr"."t_log_money"
	    WHERE "optime" >= to_number(to_date('2022-11-01','yyyy-MM-dd','GMT+9'))/1000
	    AND "optime" < to_number(to_date('2022-12-01','yyyy-MM-dd','GMT+9'))/ 1000
	    AND "moneytype"='diamond'
	    GROUP BY "rid"
      )t
ON a."rid"=t."rid" 
        WHERE "optime" >= to_number(to_date('2022-11-01','yyyy-MM-dd','GMT+9'))/1000
	    AND "optime" < to_number(to_date('2022-12-01','yyyy-MM-dd','GMT+9'))/ 1000
AND  "moneytype"='diamond';