Request to find out the date in 2 days

Question

How to write a sql query to find out that there are 2 days left before the current date. In php, this can be done via:

$res['end_date'] - time () < 86400 * 3;

How can I do the same after 1 sql query, well or better, only 2 days, if less so that it does not work out, well, if it works out, it's okay.

UPD:

It is necessary to compose a sql query that will select only those records that have 2 days left before the end_date expires

The type is int for the field end_date and is stored via the time () function in php.

Can't compose a WHERE clause.

@NicoHaase, I need to find out the dates that are 2 days before the end. How to do it via sql — FixiDens, Dec 11 '20 at 16:37
Will you be querying against a Date, DateTime, Timestamp or a unix timestamp? — Zack, Dec 11 '20 at 16:37
@FixiDens: Those are not more details. What have you tried in SQL? Which dialect of SQL? What are the field names. Etc. — KIKO Software, Dec 11 '20 at 16:38
@Zack, `Unix` - numeric type, `end_date` looks like: `1607990400`. — FixiDens, Dec 11 '20 at 16:40
@FixiDens Are you using MySQL, Postgresql, SQL Server or one of the others. This will help people provide you with inbuilt functions that might assist with your question. — Zack, Dec 11 '20 at 16:43
This might prove useful: https://stackoverflow.com/questions/39118189/sql-server-getting-before-two-days-date — Huzefa Sadikot, Dec 11 '20 at 16:48
Please share the table structure, sample input data, the expected output corresponding to that data, and your attempts — Nico Haase, Dec 11 '20 at 16:53

score 0 · Answer 1 · answered Dec 11 '20 at 17:09

You can use the FROM_UNIXTIME function to convert it to a DateTime you can then use the NOW() plus 2 days to check if the date is under 2 days. You then have to check that the date is before the current time otherwise you'll get dates that have already gone.

SELECT
    end_date
FROM 
    table
WHERE 
    FROM_UNIXTIME(end_date) <= NOW() + INTERVAL 2 DAY
AND
    FROM_UNIXTIME(end_date) > NOW()

score 0 · Answer 2 · answered Dec 11 '20 at 17:43

Assuming that you are storing an epoch timestamp (the number of seconds since January 1st, 1970), I would recommend:

select *
from mytable
where end_date >= unix_timestamp() and end_date < unix_timestamp() + 2 * 24 * 60 * 60

unix_timestamp() gives you the current epoch. You can use simple math to add two days to that.

The upside of this approach is that this does direct filtering against the store value, so this can take advantagae of an index on end_date - as opposed to converting the timestamp to a date, which requires converting the whole column before the filtering can happen. So this is much more efficient.

You can ajust the inequalities as you prefer. I used a half-open interval (inclusive on the lower bound and exclusive on the upper bound), which is a widely used approach.

score 0 · Answer 3 · answered Dec 12 '20 at 05:58

0

I ended up doing this:

$time = time();
$params = $db->query("SELECT * FROM `params` WHERE (`end_date` - {$time}) < 86400 * 3");

And it worked.

answered Dec 12 '20 at 05:58

FixiDens

29
7

Corby Jurgens · Answer 4 · 2020-12-12T11:14:13.367

I always do

select *
from mytable
where FROM_UNIXTIME(end_date) < NOW() + INTERVAL 2 DAY

This will get results where two days in the future is ahead of the end date ie, anything that will end within 2 days (or has already ended as I didn't add a check for that)

Edit: I see you can't use where If you cannot use where clause

select FROM_UNIXTIME(end_date) - INTERVAL 2 DAY as end_date 
from mytable

And then check in php if the result is before or after. This will show all results however

Request to find out the date in 2 days

4 Answers4