Mysql 和pgsql 按照每小时,每天,每月,每年,不存在数据也显示

按照每小时

• mysql

SELECT
	@date := date_add( @date, INTERVAL 1 HOUR ) AS DAY 
FROM
	( SELECT @date := DATE_ADD( '2023-09-22', INTERVAL - 1 HOUR ) FROM table_name LIMIT 24 ) t

• pgsql

select generate_series(date_trunc('hour', now()-interval '24 hour'),
                       date_trunc('hour', now()-interval '24 hour') + interval '24 hour', '1 hour') as fab_date

按照每天

• mysql

SELECT
	@date := date_add( @date, INTERVAL 1 day ) AS DAY
FROM
	( SELECT @date := DATE_ADD( '2023-09-22', INTERVAL - 1 day ) FROM table_name LIMIT 30 ) t

• pgsql

select generate_series(date_trunc('day', now()-interval '31 day'),
                       date_trunc('day', now()-interval '31 day') + interval '31 day', '1 day') as fab_date

按照每月

• mysql

SELECT
	@date := date_add( @date, INTERVAL 1 month ) AS DAY
FROM
	( SELECT @date := DATE_ADD( '2023-09-22', INTERVAL - 1 month ) FROM table_name LIMIT 12 ) t

• pgsql

select generate_series(date_trunc('month', now()-interval '12 month'),
                       date_trunc('month', now()-interval '12 month') + interval '12 month', '1 month') as fab_date

以此类推,其他的也是一样,这里不写太多了