pgsql对时间做格式化统计

将时间戳转换成可视化时间格

SELECT
    *,
    mc_db_time,
    fetch_time,
    ( mc_db_time :: BIGINT - fetch_time :: BIGINT ) AS sum_time,
    to_char( to_timestamp( fetch_time :: BIGINT ), 'yyyy-MM-dd hh24:MI:ss' ) AS format_fetch_time 
FROM
    "public"."log_history" 
WHERE
    "table_name" = 'user_master' 
    AND "primary_id" LIKE'%data-sync-%' 
ORDER BY
    fetch_time DESC 
    LIMIT 100;

针对时间做格式化筛选

SELECT COUNT
    ( user_vuclip_accounts.* ),
    EXTRACT ( DAY FROM user_vuclip_accounts.created_at ) log_day
FROM
    user_vuclip_accounts
WHERE
    EXTRACT ( YEAR FROM user_vuclip_accounts.created_at ) = EXTRACT ( YEAR FROM NOW( ) )
    AND EXTRACT ( month FROM user_vuclip_accounts.created_at ) = EXTRACT ( month FROM NOW( ) - interval '1 month' )
GROUP BY
    EXTRACT ( DAY FROM user_vuclip_accounts.created_at );