有关于时间戳的pgsql操作

create_time是timestamp类型的，我需要获取以天为单位的数据

使用to_char函数将create_time转换为char，再使用substring转换为string

SELECT
    substring(
        to_char(
            t.create_time,
            'yyyy-MM-dd hh24 : MI : ss'
        ) from 1 for 10
    ) date
FROM
    t
GROUP BY
    date
ORDER BY
    date;

 

查出每一天的时间差，sku总数，然后查询每天日期

age(MAX (create_time), MIN (create_time))是最大值与最小值之差

SELECT
 SUBSTRING (
        to_char( A .create_time,'yyyy-MM-dd hh24 : MI : ss')
        FROM
         1 FOR 10
     ) DATE,
 age(MAX (create_time), MIN (create_time)) timeDiffrent,
 SUM (qty) SKU
FROM
  A
WHERE
 SUBSTRING (
  to_char(
   A .create_time,
   'yyyy-MM-dd hh24 : MI : ss'
  )
  FROM
   1 FOR 10
 ) IN (
  SELECT
   SUBSTRING (
    to_char(
     T .create_time,
     'yyyy-MM-dd hh24 : MI : ss'
    )
    FROM
     1 FOR 10
   ) DATE
  FROM
    T
  GROUP BY
   DATE
 )
GROUP BY
 DATE
ORDER BY
 DATE;