时间段查询

-- 截取时间段
select * from user_order where
created_at between '2015-07-01' and '2018-04-5';

-- 截取时间戳，统计每天数据
SELECT count(*), date_trunc('day', created_at)
FROM user_order
GROUP BY 2
ORDER BY 2 DESC;

-- 查询24小时内注册用户
SELECT count(*)
FROM user_order
WHERE created_at >= (now() - '1 day'::INTERVAL);

SELECT count(*)
FROM users
WHERE created_at >= (now() - interval '1 month');

-- 三百六十五天时间
SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date
FROM generate_series(0, 365, 1)
AS offs;

-- 365 天内统计数据
SELECT d.date, count(se.id)
FROM ((SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date
      FROM generate_series(0, 365, 1) AS offs
     ) d LEFT OUTER JOIN
     user_order se
     ON d.date = to_char(date_trunc('day', se.created_at), 'YYYY-MM-DD'))
GROUP BY d.date;