0

I have the following data in a PostgreSQL table. It is hourly data with some hours missing. I want to insert NULL values for every missing hourly data.

Example: we have missing data for 9/9/2018 22:00. I am lost how to do this in SQL.

time             id     
----------------------------------------------------
9/9/2018 16:00  22d52ac5-6fdc-42c8-8a0e-0a7570ed6b92        
9/9/2018 17:00  6c6bc938-4a46-4bf2-a81e-81ded6f9bdb6        
9/9/2018 18:00  67c85ca5-7f85-49a1-8eb2-af6307d5277b        
9/9/2018 19:00  0d58a4d7-30ec-429c-9eb8-0f490b18a020        
9/9/2018 19:00  81870c59-410c-4d1b-8178-e5cc3f3fc4e3        
9/9/2018 19:00  a06697ee-88e6-4c26-adbb-4b59c3fe95b3        
9/9/2018 21:00  ea8a5bf8-18ff-4506-ad11-07df86c162ab        
9/10/2018 8:00  9c08ae16-3f5b-4b36-97ac-9910c9a8b6b3        
9/10/2018 8:00  fbb18c1b-92db-495c-87d0-09af9fb3d2ab        
9/10/2018 8:00  bf81644f-8ea1-474e-8fcc-26e1ff94c436        
9/10/2018 8:00  1a6b7ca3-3728-4ed0-b387-f03c46b6d597        
9/10/2018 11:00 24fbd4aa-d745-4994-a14d-93443880bdf7        
9/10/2018 15:00 7ac9376d-aee9-4c22-a794-315973aab597

Can somebody please advice with query if possible.

Improve this question
3
  • Do you just need the sql to insert null values if you have the missing hour or do you need a script to find all the missing hours? Commented Oct 8, 2018 at 5:17
  • First you need a table that lists all hours. This can be a calendar table cross joined with 23 hourly records Commented Oct 8, 2018 at 5:17
  • have you tried COALESCE ? with this you can replace the empty with null Commented Oct 8, 2018 at 9:16

3 Answers 3

Reset to default
3

Do a left outer join with this on the left side:

generate_series('2018-09-09 16:00:00'::timestamptz,
                '2018-09-10 15:00:00'::timestamptz,
                '1 hour'::interval)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Use generate_series() function for generating 24 hours and then apply left join

demo

with cte1 as
(
select '9/9/2018 16:00' as t,  '22d52ac5-6fdc-42c8-8a0e-0a7570ed6b92' as id
union
select '9/9/2018 22:00' as t,  '22d52ac5-6fdc-42c8-8a0e-0a7570ed6b92' as id

)
select distinct t1,case when t1=t::time then t else null end as t,case when t1=t::time then id else null end as id from 
(
SELECT distinct '00:00:00'::time + x * '1 minute'::interval as t1
FROM generate_series(0, 60*24, 60) AS t(x)
)a left join cte1 b on a.t1=b.t::time
Improve this answer

Comments

0

I would say:

If you can't add this functionality by software implementation in your aplication then you can add it as a trigger in your database before a new insert record is added.

I don't know what database you are using but it let you here some links:

The idea is quite simple:

  1. Check the records before an insert.
  2. Calculate the diference between the new record that you wants to insert and the last record in the database.
  3. Add the missing records you are saying.
  4. Add the new record.

I would strongly recomend you not to do so and so and solve this in your frontend or backend aplication. In the function you may have in your aplication that gathers all this data from your database can extend its functionality by including this one you are saying. With this, you won't add an irrelevant data to your storage system. You wouldn't use some space for nothing plus the increace of the database management, performance, etc.

Hope this helps,

Improve this answer

1 Comment

How do links for Oracle and MySQL help with a question for Postgres?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.