Search registry by date in mysql

Question

Good night,

I have records in my table about phases of the moon

4089    Lua nova            1983-05-12  19:26:41
4090    Primeiro Trimestre  1983-05-19  14:18:16
4091    Lua cheia           1983-05-26  18:48:29
4092    Quarto Minguante    1983-06-03  21:09:09
4093    Lua nova            1983-06-11  04:38:46
4094    Primeiro Trimestre  1983-06-17  19:47:10
4095    Lua cheia           1983-06-25  08:32:58
4096    Quarto Minguante    1983-07-03  12:14:10
4097    Lua nova            1983-07-10  12:19:30

And I would like to search in what phase of the moon was when the user was born, let’s assume that he was born on 25-05-1983 according to the records below he was born in a First Quarter on the date 19-05-1983.

I have the following query

Set FL = Conn.Execute("SELECT * FROM tbl_lua WHERE data BETWEEN '1983-05-25' AND (SELECT MAX(data) FROM tbl_lua) LIMIT 1")

But in this way it picks up the approximate record... thus appearing the full moon record on 26-05-1983

how do I get the record right?

Answer 1

Wouldn’t it be simpler this way?

SELECT * FROM tbl_lua WHERE nascimento >= data ORDER BY data DESC LIMIT 1;

Test in SQL Fiddle the version that returns moons and quarters.

Regardless of the solution adopted, just be sure to include the time in the search to make sure that the moon’s "turn" is considered and the search returns the correct data.

If you need to take only the quarter or just the moon, just create a differentiation like this:

SELECT * FROM tbl_lua
WHERE
   texto LIKE '%trimestre%' AND
   nascimento >= data
ORDER BY data DESC
LIMIT 1;

Test in SQL Fiddle the version that only returns quarters.

Or the other way round:

SELECT * FROM tbl_lua
WHERE
   texto NOT LIKE '%trimestre%' AND
   nascimento >= data
ORDER BY data DESC
LIMIT 1;

Test in SQL Fiddle the version that only returns moons.