时间戳

首页 > 解决方案 > 使用 EXTRACT 函数从时间戳获取年份或月份时出错

sql - 使用 EXTRACT 函数从时间戳获取年份或月份时出错

问题描述

我正在运行一个查询，即返回 New Users ByMonth即 The count of the first-time transactions group by Phone Number, by month& year。

这是我的查询：

select extract(year from timestamp FirstOrderDate) as yr, 
       extract(month from timestamp FirstOrderDate) as mon,
       count(*)
FROM (
   SELECT /*The column referred to below is character varying(50) - it is unfortunately not data type date*/
          TO_DATE(MIN("Date_Transaction_Occured"),'mm-dd-yyyy') AS FirstOrderDate
   FROM "TABLE1"
   group by "PhoneNumber"
) AS FirstOrderTable;
group by extract(year from date FirstOrderDate), 
extract(month from date FirstOrderDate);

这是错误：

ERROR:  syntax error at or near "FirstOrderDate"
LINE 1: ...AS total FROM (select extract(year from timestamp FirstOrder...

              ^

标签： sqlpostgresql

解决方案

删除timestamp关键字 - 仅在指定常量值时才需要，而不是在引用日期或时间戳类型的列时：

select extract(year from FirstOrderDate) as yr, 
       extract(month from FirstOrderDate) as mon,
       count(*)
FROM ...
group by extract(year from FirstOrderDate), 
         extract(month from FirstOrderDate);

推荐阅读