Timestamp Issue

[mcmuney]

I've created a new table, which looks like this:

CREATE TABLE `sc_groupie` (
  `id` int(11) NOT NULL auto_increment,
  `scm_mem_id` int(11) default NULL,
  `groupie_id` int(11) default NULL,
  `date` timestamp NULL default CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

 

The default date format is appearing like this: 0000-00-00 00:00:00

 

Question: When I write a INSERT or UPDATE code to modify the $date field, how do I need to write it? I've tried something like this, which doesn't work:

 

$time_=time();
$update = "UPDATE sc_groupie SET date='$time_' //THIS IS NOT THE FULL LINE

[angelcool]

Have you consider modifying the time within MySQL query?

 

[gizmola]

When you utilize a timestamp column in mysql there is no need to specify any type of default.  It willl already default to server time.  The only issue with using timestamp is that if the table is updated the timestamp will also be updated, so this defeats a 'createdDate' type function if the rows might be updated at some later time. 

[angelcool]

I will recommend you setting the timestamp within MySQL query, this way you do not have to deal to get the right format using PHP, though NOT impossible, it's easier to use MySQL for this.

 

For example:

$query='DELETE FROM record WHERE record_created_date < ADDDATE(NOW(), INTERVAL -2 HOUR)';

 

The preceding query delete all records that are 2 hours old.

 

Now your job is to apply the right logic for your needs; record created timestap? , record last modified(updated) timestap? this kind of questions are up to you.

 

Remember google is your friend.

 

Good luck!

 

Angel

[mcmuney]

I got it to work using the "now()" function. But now I'm trying to display it in different format. For example, by default, it displays the date as YYYY-MM-DD 00:00:00 and I'm showing in one page using this code: <?=$groupie[$i][date]?>

 

Using this code to display it, how can I modify it to show in format MM.DD.YY 00:00AM/PM?

[Mchl]

When you utilize a timestamp column in mysql there is no need to specify any type of default.  It willl already default to server time.  The only issue with using timestamp is that if the table is updated the timestamp will also be updated, so this defeats a 'createdDate' type function if the rows might be updated at some later time. 

 

No. It depends on how it's decalred.

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

[angelcool]

Quote from: gizmola on Yesterday at 10:08:08 PM

 

    When you utilize a timestamp column in mysql there is no need to specify any type of default.  It willl already default to server time.  The only issue with using timestamp is that if the table is updated the timestamp will also be updated, so this defeats a 'createdDate' type function if the rows might be updated at some later time

 

 

No. It depends on how it's decalred.

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

 

If I understood the manual you will have to use the following data type for you column in your table definition, thus it will timestamp when a record is created, but will not modify its time timestamp when updated :

 

Auto-initialization only:

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

 

95% sure, will not hurt for someone else to get the 5% left for me to be 100% sure.

 

 

 

[Mchl]

5% - provided that this is the first timestamp column in the table.