Search by date in mysql

Navigation
3

Good evening,

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 find out what phase of the moon was when the user was born, let's assume that he was born on 05-25-1983 according to the records below he was born in a First Quarter on the date 05-19-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 you get the approximate record ... thus appearing the record of the full moon on 05.05-1983

How do I get the correct record?

    
asked by anonymous 24.10.2014 / 02:15

1 answer

2

Would not that be simpler? 

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

Test the SQL Fiddle 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 "turn" of the moon is considered and the search returns the correct data.

If you need to catch 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 the SQL Fiddle version that only returns quarters.

Or the reverse: 

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

Test the SQL Fiddle version that only returns moons.

    
24.10.2014 / 02:19
Python packages with dependency installation How to update field in SQL table without blocking it?