我想从codeigniter（mysql）中的数据库表中选择最近三天的数据

Question

i have there tables in my database and i want to pick last three days of data from my 3 different database tables and i also want to show if the data is same then it will show result otherwise show empty rows in codeigniter.

my all there tables look like this ==>

id   temp   hum  rain  time
1     16    62    NR   2018-01-05 11:00:23
2     17    62    NR   2018-01-06 11:00:22
3     17    61    NR   2018-01-07 11:00:22
4     16    60    NR   2018-01-08 11:00:23

Answer 1

You can find a list of the MySQL date and time functions here. The interesting one for this question is DATEDIFF() which returns the difference between two dates in days.

It would have been helpful if you'd shown your SQL statement so far, but I'm going to guess that it's something like this:

SELECT * from my_table

To only select rows with a date within a certain range, WHERE is used. In this case, we want only the rows where the time is less than three days from now:

SELECT * from my_table WHERE DATEDIFF(NOW(), my_table.time) < 3

You might have to experiment a little to get exactly the result you want, depending on how you want to round off the days.

If you want to get the results within three days to the second, you can use the same idea, but with timestamps:

SELECT * from my_table WHERE (CURRENT_TIMESTAMP() - 60*60*24*3) < UNIX_TIMESTAMP(my_table.time)

60*60*24*3 is the number of seconds in three days.