Solved

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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