# Need PHP date help



## coljarcker (May 25, 2011)

this is driving me crazy...

I'm trying to find the difference in days between dates in a mysql database and the current date. but I'm getting hung up on the formatting. I'm stuck with getting dates that are formatted "M d, Y". example:

Apr 30, 2011

so I pull the one date from the database, and store it in, say $date1.

rather than post my failed attempts so far, I'll save you the hassle and just ask what's the best way to do this? get today's date, then find the difference in days between today and $date1, it's gotta be so simple but it eludes me.....


----------



## W1zzard (May 25, 2011)

SELECT *, UNIX_TIMESTAMP(ts) AS tsUnix FROM live_chatlog ORDER BY ts DESC LIMIT 20";

mysql can give you its date columns as unix timestamp (which is just seconds since 1970). 
php time() gives you a timestamp too. subtract those, divide by (24*3600) and you get the number of days difference

an extremely useful function for other date calculations is strtotime() because "next month" is not "current month + 31 days, no 30, no 28, wait 29, what about leap second"


----------



## coljarcker (May 25, 2011)

thank you for helping. unfortunately the date is stored in the database as a varchar and I can't change that. actually, I'm pulling it from an array that's already been created for the rest of the site, in this case the date is stored in *$user[2]*.

so I can get the current time stamp by using *time()*, but how can I get the date (stored as text string) in _$user[2]_ converted to a timestamp so I can perform the subtraction?

thanks again for your help W1zzard. I apologize for being so thick headed about this....


----------



## Disparia (May 25, 2011)

You missed the part in his post about strtotime().

It can take in a string such as "Apr 30, 2011" and return the unix timestamp, in this case: 1304114400.


```
$ts = strtotime( $user[2] );
```

If you're on 5.3.0 or greater, new methods have been added to datetime: http://www.php.net/manual/en/datetime.diff.php


----------



## W1zzard (May 25, 2011)

whats the format of the date in your database ?


----------



## coljarcker (May 25, 2011)

Jizzler said:


> You missed the part in his post about strtotime().
> 
> It can take in a string such as "Apr 30, 2011" and return the unix timestamp, in this case: 1304114400.
> 
> ...



ahh, that works great! sorry I missed it. thank you both!

I am stuck with 5.2.1.7 right now. I will check out that link for when I do get upgraded.



W1zzard said:


> whats the format of the date in your database ?



in the database it is stored as "M d, Y" in a varchar(20) value. thanks again for your help.


----------



## Thrackan (May 25, 2011)

coljarcker said:


> ahh, that works great! sorry I missed it. thank you both!
> 
> I am stuck with 5.2.1.7 right now. I will check out that link for when I do get upgraded.
> 
> ...



Any chance you can store the date in a proper DATE format? Varchar stored dates are a nightmare.


----------



## W1zzard (May 25, 2011)

coljarcker said:


> in the database it is stored as "M d, Y" in a varchar(20) value



strtotime should convert that just fine. the better approach is to store your data in the appropriate field types. 

how do you plan on sorting that field? e.g. for statistics or reports


----------

