A php- mysql problem- date formats problem

[Aji]

Hi all,

I am having a small problem, I am entering the date to mysql database as date('F j, Y, g:i a') but in string format. The database field type for date is varchar(255).

Now I want to search from a particular date(mm/dd/yyyy) to a particular date(mm/dd/yyyy), how will I do that?

let me explain again

Enter to mysql
$enterdate=date('F j, Y, g:i a');
insert into datetable(DateFLD, NameFLD) values ($enterdate,'$_POST[FirstName]');
Here the field DateFLD is varchar(255)type.

Problem
Search between mm/dd/yyyy and mm/dd/yyyy

Thanks
Aji

[robwatts]

Hi Aji

Dates are notoriously problematic to work with. Here are a couple of ideas which may assist you in reaching your objectives

One immediate suggestion that might work is to convert the dates to a unix timestamp and then compare the numbers using some kind of comparison.



http://www.php.net/strtotime

(dates converted at http://www.4webhelp.net/us/timestamp.php)

Consider today

Thursday, March 18th 2004, 22:14:02 (GMT) translates to 1079648042

and Friday last week

Friday, March 12th 2004, 21:36:18 (GMT) translates to 1079127378

$thursday18mar2004="1079648042";

$fri12mar2004="1079127378";

$pointinthefuture="1080127378"

$pointinthepast="1065123738";

Id suggest that you consider converting the dates recorded in to a unix timestamp and then put them into a seperate additional field within your db eg insert the converted var named $unixdatefield into a field named unixdatefield.

$unixdatefield=$thursday18mar2004;

which you could then use like this

select * from table where unixdatefield is >= $pointinthepast and $datefield is <= $pointinthefuture;

The above equates to get all from the db table where the data in the field named unixdatefield is greater than your specifed starting date but less than your specified ending date, effectively, in between. Though the BETWEEN operator in mysql may also achieve the same.

Using this approach you can then compare the data using the timestamps as opposed to comparing days dates and times, or other unconverted data.

That said, there may not be a reason why you couldnt set a start date and finish date in the format you describe and then pull data from your db based on values that were equal to or larger (=>) than start but less than or equal to (<=)end date.

Although, again, by using the timestamp approach and treating the dates as integers itll probably be more likely to work.


Far from perfect, and Im sure theres a far more elegant or better approach available, but in any case I do hope it helps.

Good luck.

[WilliamC]

As robwatts said use the epoch timestamp for all dating. It works wonders and is much simpler to process for. Also when using epoch make sure the cell type is int(10) so mysql knows it is strictly numerical.

[WebSavvy]

If you're using a newer vesion of MySQL (4+) there are new query commands that are supported that you can make use of.

TO_DAYS
mydatediff

I use the same date formats as you do. Just within the past week I needed to get a set of data that happened between $start_date and $date_q

$date_q = the date current, for which I wanted the information to

I ended up running the query this way, and it works perfectly.

$qResult=mysql_query("SELECT TO_DAYS(MAX(start_date))-TO_DAYS(MIN(date_q)) as mydatediff FROM table");

echo mysql_result($qResult,0,"mydatediff");
mysql_close($connection);


The fields start_date and date_q are actual fields within my table.

You'd of course have to insert your own db fields, and then play around with it, and see if you can get it to work for you.

Good luck.

[Aji]

I am planning to change the database (table) field to time format. This will make my work easier and thanks for all the help, will trouble you with more doubts in the near future.

Thanks once again
Aji