[SOLVED] Days Passed

[KingOfHeart]

I use this to set up my dates

date("YmdHis");

 

When I save it to the mysql I use var char(if this makes any difference)

The field name is "lastupdate"

 

So how can I return how many days has passed since "lastupdate"

[Mark Baker]

This would be a whole lot easier if you modified your database to hold lastupdate as a datestamp rather than a varchar2

[KingOfHeart]

I'd rather not change it.

If each number was separate (I can do this) would this help you create the formula?

[ngreenwood6]

I agree with mark baker. You should really use a timestamp. With dates you can use the built in php functions to achieve what you would like to do. If you use this method what happens if it is a leap year. Then your calculations will be off. With the built in php functions it will take that into account.

[corbin]

I agree with mark baker. You should really use a timestamp. With dates you can use the built in php functions to achieve what you would like to do. If you use this method what happens if it is a leap year. Then your calculations will be off. With the built in php functions it will take that into account.

 

 

Errr.... I think he meant a MySQL datetime column (or a date column).

[ngreenwood6]

Sorry about that. Read that wrong. I always store my dates as a timestamp in (int) format. You can pretty much do whatever you want with them then, pretty easily.

[KingOfHeart]

What php command can I use then?

 

$lu = "20081103224334";

$year = $lu[0] . $lu[1] . $lu[2] . $lu[3];

$month = $lu[4] . $lu[5];

$day = $lu[6] . $lu[7];

 

I can easily move around the numbers in any order that is needed, guys.

 

edit: I found exactly what I needed.

 

$lu = "20081103224334";
$year = $lu[0] . $lu[1] . $lu[2] . $lu[3];
$month = $lu[4] . $lu[5];
$day = $lu[6] . $lu[7];
$time = time() - mktime(0, 0, 0, $month, $day, $year);
$days = floor($time/86400);
echo $days;

 

I knew it was not that hard to do, so don't see why you did not suggest this for me. Sorry if I sound harsh guys.

 

*Thread Closed*

[Mark Baker]

I knew it was not that hard to do, so don't see why you did not suggest this for me. Sorry if I sound harsh guys.

Don't mean to sound harsh back, and don't see why you ignored what we recommended, but we were suggesting a far better method for you.

[corbin]

Sorry about that. Read that wrong. I always store my dates as a timestamp in (int) format. You can pretty much do whatever you want with them then, pretty easily.

 

 

I used to use unix timestamps for stuff too, and then one day in the MySQL forum, it smacked me in the face.

 

I'm sure there are other things too, but for example, let's say you're making a hit counter...  How would you find hits on Thursdays?

[ngreenwood6]

I'm sure there are other things too, but for example, let's say you're making a hit counter...  How would you find hits on Thursdays?

 

Couldn't you use the date() for that to set the day as thursday?

[corbin]

As far as I know, you would have to convert the unix timestamp to a date or datetime column before MySQL could do something useful with it.

[ngreenwood6]

I think you are wrong but then again I could be wrong lol. but wouldnt this give you the day of the week in numeric form:

 

$day_of_week = date("w", $timestamp);

 

Then you could simply compare that to whatever the day was you wanted to compare it to.

[corbin]

I think you are wrong but then again I could be wrong lol. but wouldnt this give you the day of the week in numeric form:

 

$day_of_week = date("w", $timestamp);

 

Then you could simply compare that to whatever the day was you wanted to compare it to.

 

 

Sorry about that. Read that wrong. I always store my dates as a timestamp in (int) format. You can pretty much do whatever you want with them then, pretty easily.

 

 

Good luck comparing a string representation of a week index to a unix timestamp.

[ngreenwood6]

ok let me give you an example that maybe you can understand what I am saying. Here it goes:

 

<?php
//make the database connection
$connect = mysql_connect("host", "user", "pass");

//select database
mysql_select_db("database");

//make the query
$query = "SELECT timestamp FROM table";

//perform the query
$results = mysql_query($query);

//the date thursday has a numeric value of 4

//initialize counter
$counter = 0

//compare against database for hit counter
while($row = mysql_fetch_array($results))
{
$day_of_week = date("w", $row['timestamp']);
if ($day_of_week == "4")
{
$counter++
}
}

echo $counter

?>

 

Doesn't that do what you are trying to do by getting the hits for that day?

[corbin]

Do me a favor.

 

Fill a table with 1,000,000 random rows, run that script, and tell me how long it takes.

 

 

Then tell me how long this would take:

 

 

SELECT COUN(*) FROM hits GROUP BY DAYOFWEEK(timestamp);

 

 

 

(Of course the fastest solution would be to pre store the day of the week, but we're assuming that situation hasn't been done.)

[ngreenwood6]

Just want to make a note of your initial statement:

 

I'm sure there are other things too, but for example, let's say you're making a hit counter...  How would you find hits on Thursdays?

 

You said how would you find hits on a thurday. I showed you how. You were acting like it isn't possible and I was just letting you know that it is possible. I never said that it was the fastest or the best method but it is possible.

[corbin]

Fair enough.

 

 

 

 

 

But, to be technical, I meant how would you find the days that were a Thursday using only MySQL.  (Also possible though, and not what I said.)

[Mchl]

There's always FROM_UNIXTIME()

[corbin]

Yeah, that's what I was alluding to when I said it's possible, but why have to convert it when you could just store it in what you're going to convert it to?  It would of course be easier to use in PHP as a timestamp though, so in some cases I guess it would make more sense to store it in a timestamp format.  Only when not manipulating it in MySQL though.

[Mchl]

I remember one benchmark at MySQL Performace Blog, which showed that for _some_ queries timestamp stored as integer is faster. So I guess it's fine if you know what you're doing.

[corbin]

I would be willing to bet that those queries did not convert it to a datetime column ;p.  But yeah, a timestamp can of course be better in some situations.  I would probably use an integer timestamp for a registration time, for example (unless I ever planned on doing stats off of the registration times...).