MySQL Forums
Forum List  »  Newbie

Subdivide data collected by day
Posted by: Michele Turchiarelli
Date: November 22, 2019 05:26PM

Hi.

I have a database which stores, for each day, several updates about the weather (for example, 1 update every 30 minutes).

Example:

[id] [datetime] [weather]
1 2019-11-22 18:11:53 sunny
2 2019-11-22 20:25:45 cloudy
3 2019-11-22 22:12:34 sunny

4 2019-11-23 00:41:53 sunny
5 2019-11-23 01:11:10 sunny
6 2019-11-23 02:45:20 cloudy

As you can see there are 3 samples for each day.
They are a few, but imagine the real case when there are like hundreds of samples for each day.

Now, what I want to do is make a PHP page which is able to list, for each day, all the samples

Example:

* 22nd November 2019

- 18:11:53 it was sunny
- 20:25:45 it was cloudy
- 22:12:34 it was sunny

* 23rd November 2019

- 00:41:53 it was sunny
- 01:11:10 it was sunny
- 02:45:20 it was cloudy

How do I achieve something like this in SQL?

My extreme idea was to store in a second table all the days present in the original table, so that I can just "loop" over all the days and then filter based on that day in the original table.

Any better idea?
Thanks in advance!

Options: ReplyQuote


Subject
Written By
Posted
Subdivide data collected by day
November 22, 2019 05:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.