Jump to content
Larry Ullman's Book Forums

Timestamps And Datetime?


Recommended Posts

I wanted to ask when should we use TimeStamps as apposed to DateTime's in MySQL?

 

I read somewhere that a table should only have one TimeStamp but in some of the Larry books i found situations in where it was used twice in a table. The reason i ask this question is because i had a few tables with more than one TimeStamp in but now Yii doesn't work in updating a Timestamp and i don't know why? May be someone has a quick solution here to a supposably easy problem.

 

You have probably seen Larry use these two situations:

 

1. registration_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 

2. date_updated TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

Link to post
Share on other sites

This is definitely a matter of personal preference and you may hear several different answers. Part of the issue is based upon the version of MySQL in use. Depending upon the version, MySQL may only be able to do an automatic update on one TIMESTAMP column (normally the first). Other versions allow you to do multiple automatic setting of TIMESTAMP column values (I believe). 

 

In any case, I don't think Yii ever automatically updates a date and/or time column unless you change your model for it to do so.

  • Upvote 1
Link to post
Share on other sites

Thanks for your good answer Larry! Yes you are correct Yii doesn't update timestamp columns automatically but i had added my own rules for this to the models which failed to update. I did plan stuff out for my project before started but i have had to make unexpected changes anyway. The trouble is right now i am also not sure whether i will need timestamps or not with certain things as i am aware a lot of the time functions require this format. On the otherhand there are other time functions which i need which php doesn't provide.

Link to post
Share on other sites

If everything else fails, you could build a solution into your base model. Hook into something like the afterSave filter (or what it is called) and do a manual update using a formated DateTime object.

 

I am aware of all of those things but sometimes we need timestamps. You can even add your own behaviours with timestamp rules did you know about that?

Link to post
Share on other sites

Oh and by the way Dr.Thomas, have you found that you can hardcode stuff in a few places first before you later  create helper class functions. Or you may have some model methods that are similar and then just decide to cut those out and later replace with one helper class function. It seems to me that those models and so can be like some drawers in your room and you change the items about in them to find the best order. I don't think its really possible to plan for the larger projects unless you have a lot of experience and then again its probably not possible to get it perfectly correct.

Link to post
Share on other sites

Then just format the DateTime object like a timestamp? Not really a problem. :)

 

Well its a problem that is taking your more than 15mins to solve from now, haha. Okay ill see you later i need to get onto my project.

Link to post
Share on other sites

I wasn't giving you any attitude, i am actually in a good mood here, its just you stated something so i wanted to know how you would do that, as i will need to make the TimeStamp format also, just wanted to see your solution that's all. I was highly applauding you as i even called you Dr.Thomas. Please give me your solution i would appreciate it.

Link to post
Share on other sites

This is definitely a matter of personal preference and you may hear several different answers. Part of the issue is based upon the version of MySQL in use. Depending upon the version, MySQL may only be able to do an automatic update on one TIMESTAMP column (normally the first). Other versions allow you to do multiple automatic setting of TIMESTAMP column values (I believe). 

 

In any case, I don't think Yii ever automatically updates a date and/or time column unless you change your model for it to do so.

 

Okay Larry i was checking in your javascript book in the final chapter.

 

In your items table you use

 

dateOpened timestamp NOT NULL,

dateClosed datetime NOT NULL,

 

What is your reason for using timestamp for one column as apposed to just using datetime for both table attributes?

Link to post
Share on other sites

Well. You laugh of me for not solving your problem in 15 minutes and you most likely called me "Dr." sarcastically. Why shouldn't I take offense to that? I HAVE given you a way to handle the functionality you describe, but most likely YOU can implement it way faster than me. It's as simple as that you know YII — I don't... You can't expect me to pull a fully functional implementation of this out of my ass on the spot.

 

1. Functionality goes into your extended base model

2. Set a protected flag property in the base model to turn auto timestamps on and off.

3. Write a protected method like getUpdatedTimeStamp. It could look something like this:

protected function getUpdatedTimeStamp()
{
   $datetime = new DateTime("now");
   return $datetime->getTimeStamp();
}

4. Implement a method for the onAfterSave filter. (or similar)

5. In that method, check if the flag is set.

6. If the flag is set, assign the timestamp method to the updated_at property

7. Save the model again.

 

The alternative is to use other filters like beforeSave, if that makes sense. You could possibly do the assigning of timestamps in one operation.

 

Again, Edward. This is the way I would solve things personally. I don't know if that's the perfect way, but it WILL work. As I stated earlier, if nothing else works (namely the possibly tried and tested solutions you find online) this is at least a simple way to implement it. I don't know YII, and won't pretend I know it's ins and outs. In lack of solutions, and since you asked, I tried to be a nice guy and gave you a general purpose solution that will most likely work. I don't think I deserve the tone you are giving me.

  • Upvote 1
Link to post
Share on other sites

Thanks but with regards to Yii am already aware with the functionality where and when to do things without that knowledge i would not be as far as i am now. I was just curious for the timestamp only, sometimes you write more than you need to but thanks anyway.

 

Also with a function like yours in model classes would not be suitable for storing the method as it would be required from many of the models especially in my site, so a helper class would be more appropriate storage.

 

With regards to code there are also things for myself which i think are easy but i land up spending a lot more time on. Or there are solutions online which i have used and then later found to be not complete so they have to be recoded. Its difficult to be perfect and especially to understand what someone else is doing. Thanks for your answer and please be positive i meant no offence to you.

Link to post
Share on other sites

This is definitely a matter of personal preference and you may hear several different answers. Part of the issue is based upon the version of MySQL in use. Depending upon the version, MySQL may only be able to do an automatic update on one TIMESTAMP column (normally the first). Other versions allow you to do multiple automatic setting of TIMESTAMP column values (I believe). 

 

In any case, I don't think Yii ever automatically updates a date and/or time column unless you change your model for it to do so.

 

After running further tests i was able to get the records to update in the database

 

I used this in Model Rules()

 

array('date_updated', 'default', 'value'=>new CDbExpression('NOW()'), 'on'=>'update'),

 

I just want to point out that it did not work for:

 

date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

date_updated TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

 

but it did work for:

 

date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

date_updated TIMESTAMP NULL,

 

or

 

date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

date_updated DATETIME NULL,

Link to post
Share on other sites

Antonio with your databases do you use timestamps or only datetime's? Do you see a reason why we need to use a timestamp other than datetime.

 

I am the kind of person that likes to see consistency with my work so this situation for me is still not clear.

Link to post
Share on other sites

Okay i see the importance of TIMESTAMP NOW,

 

http://stackoverflow.com/questions/409286/datetime-vs-timestamp

 

Very Important Point

 

"In MYSQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)"

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'america/new_york' where DATETIMEis unchanged.

Well this is the time for me to find that good old famous book, "PHP and MySQL for Dynamic Websites and check out UTC time zones.

 

Thanks Antonio and Larry i will be able to work out the rest on my own.

Link to post
Share on other sites

Well. You laugh of me for not solving your problem in 15 minutes and you most likely called me "Dr." sarcastically. Why shouldn't I take offense to that? I HAVE given you a way to handle the functionality you describe, but most likely YOU can implement it way faster than me. It's as simple as that you know YII — I don't... You can't expect me to pull a fully functional implementation of this out of my ass on the spot.

 

1. Functionality goes into your extended base model

2. Set a protected flag property in the base model to turn auto timestamps on and off.

3. Write a protected method like getUpdatedTimeStamp. It could look something like this:

protected function getUpdatedTimeStamp()
{
   $datetime = new DateTime("now");
   return $datetime->getTimeStamp();
}

4. Implement a method for the onAfterSave filter. (or similar)

5. In that method, check if the flag is set.

6. If the flag is set, assign the timestamp method to the updated_at property

7. Save the model again.

 

The alternative is to use other filters like beforeSave, if that makes sense. You could possibly do the assigning of timestamps in one operation.

 

Again, Edward. This is the way I would solve things personally. I don't know if that's the perfect way, but it WILL work. As I stated earlier, if nothing else works (namely the possibly tried and tested solutions you find online) this is at least a simple way to implement it. I don't know YII, and won't pretend I know it's ins and outs. In lack of solutions, and since you asked, I tried to be a nice guy and gave you a general purpose solution that will most likely work. I don't think I deserve the tone you are giving me.

 

Just want to say thanks very much, i managed to create a difference time function that returned the format "2 Years 4 Months 2 Days". I found some horrible solutions for this online. The DateTimeInterval Object created from Datetime has certainly done me the trick. That's why i don't call you Dr. Thomas for nothing, thanks Antonio. (PS. I won't call you that anymore, sorry Antonio)

Link to post
Share on other sites

Glad you worked that one out. I did something very similar to you with a time range difference function, but with conjugations for the human-readable output. That means 1 hour - 2 hours, 1 second, 2 seconds, etc. I also added different output depending on how much time is remaining. It'll return days, hours, minutes or minutes and seconds when time < 10 min.

 

I added a small gist here for you: https://gist.github.com/thomaslarsson/6029616

 

The class is not really good code by any stretch of the imagination, but it does the trick for me.  Would love to see your solution for this too. :)

  • Upvote 2
Link to post
Share on other sites

You have made a cool resuable class, i can see you are further with your project than i am, probably as you knew CI when i was learning Yii. At the moment i only need this function in one model but if get it occuring in repeated area's i was going to make it part of a helper class. I currently only have a Image Manipulation Helper Class.

 

Here is my code:

/**
	 * Get the time user has been a member for in the format years, months, days
	 * @return string of the time
	 */
	public function getMemberTime()
	{
		$d1 = new DateTime($this->registration_date);
		$d2 = new DateTime();
		$dateIntervalObj = $d2->diff($d1);
		
		$memberTimeArray = array();
		
		if($dateIntervalObj->y > 0) $memberTimeArray[] = ($dateIntervalObj->y == 1) ? '1 Year' : $dateIntervalObj->y.' Years';
		if($dateIntervalObj->m > 0) $memberTimeArray[] = ($dateIntervalObj->m == 1) ? '1 Month' : $dateIntervalObj->m.' Months';
		$memberTimeArray[] = ($dateIntervalObj->d <= 1) ? '1 Day' : $dateIntervalObj->d.' Days';
	
		return implode(' ',$memberTimeArray);
	}

I haven't taken into account errors and exceptions as there will definetely be a datetime value and timestamp in the model otherwise we wouldn't get this far on my site.

 

I saw your website link from there its pretty cool, you and your friends look similar to people in my family. I don't know what happened to me my Dad has blond hair and blue eye's and i got Dark Brown hair and eye's. I turned out to be the Hell Boy :blink:

 

Hey ive just realized you have the same sort of bug that is on my site, the page shifts left, now i see why because of the vertical scroll bar appearing on the right on Mozilla FireFox. I thought it was a CSS issue and overlooked the scroller.

Link to post
Share on other sites

Nice solution. You write some really good code, Edward. The only thing I don't get is the array in your method? Why not do simple String concatenation instead of imploding an array in the end?

 

My reasoning for adding exception handling is more about my Java background. If you have uncaught exceptions in Java, your programs will crash, so it's become something of a habit. I tend to always catch exceptions, even when it's not strictly needed. Thanks for the nice words about the site. The site is pretty much finished, and we will remove dummy data and start advertising the system quite soon.

 

Dark eyes are the dominant gene, so blue eyes requires both parents to at least have a recessive blue gene, and for that to be "picked". The same with blond hair. My sister got brown hair and blue eyes, and we have the same parents of course.

Link to post
Share on other sites

I did orginally start with a string but you see what if we have 0 Years, 0 Months etc, i don't want to write that out on the screen, so therefore i just just and array then imploded the implements that were created. If you code it via a string and concatenation it takes a lot more code and you have to set variables to null and flags and stuff. The above code did the trick much easier. Love you oop code also its really cool, now i have loads going on everywhere so i guess later i will need to make some good classes, ha once i find out what is going on? hehe

 

I have used exceptions in other parts of my code but not for this part, i guess when i give the site the run live i will see what happens, if i get stuff coming up in the error log or people filling out the bug report form ill go back and fix them. Looking forward to seeing what will happen when my site goes live.

 

Oh, i wish i could of had blue eye's everyone has dark eyes, nothing special these days.

 

I am working on a countdown timer right now, just like the one on ebid.net, check it out on this page.

 

http://as.ebid.net/for-sale/vodafone-huawei-ascend-g300-pay-as-you-go-smartphone-111862013.htm

 

One thing i am wondering is how often should i call stuff a function with js every half second? I am wondering what sort of impact this is going to have on the overall performance of the website.

 

Hehe Larry, anything I want to code, anything, i can get it done now! :lol:

Link to post
Share on other sites

Actually i could of use strings and concatenation but now i remember why i did the array because first i used comma's between the times so 4 Years, 2 Months, 1 Day, so for that method it was difficult without the array. Later i changed my mind and decided to use spaces. I could change it back to strings but actually i prefer the array method now that its done, it doesn't really matter. The thing is there are so many parts of code nothing can be completely perfect so i will make do with that.

  • Upvote 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...