mysql show the record that is current as of date

Question

mysql show the record that is current as of date

Navigation

#1 by (1 votes)

0

Hello friends could you help me? I have these records:

+-----+------------+------------+
| id  | date_start | date_end   |
+-----+------------+------------+
| 181 | 1511553600 | 1511899200 |
| 186 | 1512158400 | 1512504000 |
| 191 | 1512763200 | 1513108800 |
| 196 | 1513368000 | 1513713600 |
| 201 | 1513715400 | 1514136600 |
| 206 | 1515340800 | 1515427200 |
| 211 | 1515945600 | 1516032000 |
| 216 | 1516550400 | 1516636800 |
| 221 | 1517155200 | 1517241600 |
| 226 | 1517760000 | 1517846400 |
| 231 | 1518364800 | 1518451200 |
| 236 | 1518969600 | 1519056000 |
| 241 | 1519574400 | 1519660800 |
| 246 | 1519833600 | 1519920000 |
| 251 | 1520179200 | 1520265600 |
| 256 | 1520784000 | 1520870400 |
| 261 | 1521388800 | 1521475200 |
| 266 | 1522594800 | 1522681200 |
| 271 | 1523199600 | 1523286000 |
| 276 | 1523804400 | 1523890800 |
| 281 | 1524063600 | 1524150000 |
| 286 | 1524409200 | 1524495600 |
| 291 | 1525014000 | 1525100400 |
| 296 | 1525618800 | 1525705200 |
| 301 | 1526223600 | 1526310000 |
| 306 | 1526828400 | 1526914800 |
+-----+------------+------------+

I have the start and end date in timestamp format, as I could do a query to show me the closest or current record, to the current date ie show the record when date_start is between now () and date_end but also show the record when now () is between date_start and date_end.

since I always need to have the one next or the one that is happening,

I have these questions:

SELECT id FROM tabla1 WHERE (date_start BETWEEN unix_timestamp() AND date_end) order by id asc limit 1;
SELECT id FROM tabla1 WHERE (unix_timestamp() BETWEEN date_start AND date_end) order by id asc limit 1;

But I do not know how to join them to show me the current record that is id 186 Could you help me please?

greetings

mysql timestamp date query

asked by skycomputer2 04.12.2017 в 23:31

source

1 answer

Loguin API AOL Token connect to localhost from a device that is in the same network use XAMPP

score 1 · Answer 1

According to your statement

show the record when date_start is between now () and date_end but also show the record when now () is between date_start and date_end.

That translates to:

those who finish after the present AND (begin after the present OR start before the present).

The second condition covers all possible cases, so you only need to find the one that ends sooner than the one that has not finished

SELECT id 
FROM tabla1 
WHERE (unix_timestamp()<date_end) 
ORDER BY date_end ASC 
LIMIT 1;

This assuming that the time lapses do not overlap.