Solved

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

Posted on 2004-08-04
5
410 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

860 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