How to generate series of 24hrs with 1 hour interval and display the last as 23:59:59

0
votes

Project: BIRT
Datasource: Amazon Redshift

I want to generate a Data Set with value of:

00:00:00
1:00:00
2:00:00
3:00:00
4:00:00
5:00:00
6:00:00
7:00:00
8:00:00
9:00:00
10:00:00
11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00
18:00:00
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00
23:59:59 //the last value should display like this

I was able to generate a series of 24hours with 1 hr interval, but I need to make the last one's value as 23:59:59

Query to generate 24 hours with 1 hour interval:

SELECT start_date + gs * interval '1 hour' as times
FROM (
SELECT '2019-05-21 00:00:00'::timestamp as start_date, generate_series(1,24, 1) as gs)

How is that?

Thanks

2
sqlamazon-redshiftbirt
Could you show the code you already wrote to generate your series? It will be easier to update if known. In short: nest it inside a cte with something like: if hour > 23:00:00) then hour -interval 1 second else hour. – Guillaume
Note that the 23 intervals are 60 minutes, and the last interval is 59 minutes 59 seconds. Also, why do you need this? – 9000
Figured it out, just add the default value: 23:59:59 to the report parameter, in my case I use this data set to bind/use to my report parameter – NicoTing

2 Answers

0
votes

Updating your query, just adding a if for the last hour:

SELECT 
    start_date + gs * interval '1 hour' 
  - if(gs=24, interval '1 second', interval '0 second') as times
FROM (
  SELECT 
    '2019-05-21 00:00:00'::timestamp as start_date
    , generate_series(1,24, 1) as gs
)
0
votes

I think too much about this, the simplest way to achieve this is just add a default value on the report parameter , if you're going to use the data set in the report parameter

or with this:

SELECT start_date + gs * interval '1 hour' as times
FROM (
SELECT '2020-01-01 00:00:00'::timestamp as start_date, generate_series(1,24, 1) as gs)
union
select '2020-01-01 23:59:59'::timestamp as start_date