Postgres generate series with exactly 100 steps-CodePudding

Lets say we have the dates

'2017-01-01'
and
'2017-01-15'

and I would like to get a series of exactly N timestamps in between these dates, in this case 7 dates:

SELECT * FROM 
    generate_series_n(
        '2017-01-01'::timestamp,
        '2017-01-04'::timestamp,
        7
    )

Which I would like to return something like this:

2017-01-01-00:00:00
2017-01-01-12:00:00
2017-01-02-00:00:00
2017-01-02-12:00:00
2017-01-03-00:00:00
2017-01-03-12:00:00
2017-01-04-00:00:00

How can I do this in postgres?

CodePudding user response：

You can divide the difference between the end and the start value by the number of values you want:

SELECT * 
FROM generate_series('2017-01-01'::timestamp, 
                     '2017-01-04'::timestamp, 
                     ('2017-01-04'::timestamp - '2017-01-01'::timestamp) / 7)

This could be wrapped into a function if you want to avoid repeating the start and end value.

CodePudding user response：

Possibly this can be useful, using the generate series, and doing the math in the select

select '2022-01-01'::date   generate_series *('2022-05-31'::date - '2022-01-01'::date)/15 
FROM generate_series(1, 15)
;

output

  ?column?
------------
 2022-01-11
 2022-01-21
 2022-01-31
 2022-02-10
 2022-02-20
 2022-03-02
 2022-03-12
 2022-03-22
 2022-04-01
 2022-04-11
 2022-04-21
 2022-05-01
 2022-05-11
 2022-05-21
 2022-05-31
(15 rows)

CodePudding user response：

WITH seconds AS 
(
    SELECT EXTRACT(epoch FROM('2017-01-04'::timestamp - '2017-01-01'::timestamp))::integer AS sec
), 
 step_seconds AS
(
    SELECT sec / 7 AS step FROM seconds
)
SELECT generate_series('2017-01-01'::timestamp, '2017-01-04'::timestamp, (step || 'S')::interval)
FROM step_seconds

Conversion to function is easy, let me know if have trouble with it. One problem with this solution is that extract epoch always assumes 30-days months. If this is problem for your use case (long intervals), you can tweak the logic for getting seconds from interval.