pgsql hive mysql生成时间序列

mysql生成时间序列

SELECT * from  (
SELECT
	@cdate := DATE_ADD( @cdate, INTERVAL + 1 DAY ) cday 
FROM
	( SELECT @cdate := DATE_ADD( '2018-01-01', INTERVAL - 1 DAY ) FROM bi_pos_record ) t0 
WHERE
	date( @cdate ) <= DATE_ADD( '2022-11-14', INTERVAL - 1 DAY )
	)

bi_pos_record 为你数据库中的任意一个表

hive生成时间序列

    SELECT
        pos,
        date_add( start_date, pos ) dd
    from ( SELECT '2018-01-01' AS start_date, '2022-11-14' AS end_date ) temp
        lateral VIEW
        posexplode ( split ( space( datediff( end_date, start_date ) ), '' ) ) t AS pos, val

标题pgsql自动生成时间序列

按天

SELECT
to_char ( c, 'yyyyMMdd' ) AS AllDate
FROM
generate_series (to_timestamp('2019-08-21', 'yyyy-MM-dd'),to_timestamp('2020-04-12', 'yyyy-MM-dd'),'1 day' ) AS c

按月

SELECT
to_char ( c, 'yyyyMM' ) AS AllDate
FROM
generate_series (CURRENT_TIMESTAMP - INTERVAL '5 MONTH', CURRENT_TIMESTAMP,'1 month' ) AS c