Solved

Date base of dates - what is the closest date to the current date?

Posted on 2004-08-04
5
404 Views
Last Modified: 2012-06-27
Hi I am using a sort date function in PHP like this:
$result = mysql_query("select * from shows ORDER by showdate");
Now what I need to do is a comparison of the current date to the dates in my data base.
I need to know the number of the row that is closet to the current date. Like this

todays date = 08.04.04

Before sort:
month.day.year.
07.11.04
09.14.04
08.02.04
08.07.04

afrer sort:
07.11.04
08.03.04
08.07.04
09.14.04

The number of the row that is closest to the curret date is "2" (08.03.04)

I need this number so I can pass it on to a data grid so it will automatically select the date closest to the current date. Can this be done?

Thanks,

Waffe






0
Comment
Question by:waffe
  • 2
5 Comments
 
LVL 7

Expert Comment

by:madwax
ID: 11723691
How do you store the dates in the database? If you store them as timestamps (@see: http://www.experts-exchange.com/Databases/Mysql/Q_21082709.html) I would guess you could do as follows, select the date with the smallest difference to the current date, this is easily done i sql. Just an example:

SELECT id,MIN((UNIX_TIMESTAMP()-myTimestamp)) AS diff FROM `test` GROUP BY myTimestamp ORDER BY diff

where the table looks like:
test(id*, timestamp)

The query returns the id of the tuple with the smallest difference in time.

N.B!. you will have to modify it to work 100%, e.g. how to react for future dates which now return a -number and so on. But I think this would be the way I would try to do it. You

You might also want to check out the MySQL function: DATEDIFF (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) but it only counts days...

Good luck,
//madwax
or
0
 
LVL 7

Accepted Solution

by:
madwax earned 100 total points
ID: 11723695
correction table strcuture:
test(id*,myTimestamp)

sorry... =/
0
 
LVL 18

Assisted Solution

by:arantius
arantius earned 100 total points
ID: 11748490
Run these two queries:
SELECT showdate FROM shows WHERE showdate>=NOW() ORDER BY showdate DESC LIMIT 1
SELECT showdate FROM shows WHERE showdate<=NOW() ORDER BY showdate DESC LIMIT 1
Extract the two dates. Turn them into a timestamp thusly:
mktime(
  substr($time, 11, 2), substr($time, 14, 2), substr($time, 17, 2),
  substr($time, 5, 2), substr($time, 8, 2), substr($time, 0, 4)
);

Compare those two values to the value of time() in PHP.  The closer value is the closer date.  Manipulate that timestamp as you will, perhaps with the date() function.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now