Dates with Destiny

rcoursey

Simple questions:

Which is the best date type to work with, timestamp or datetime?

Are there instances where one is more suitable than the other?

mnewhart

It depends on what you need it for. Timestamp is nice if you don't want to add the column to an insert. It will automatically put the date/time in for you. The reverse holds true for DateTime. If you don't put it, you won't get it. Also, Timestamp has a smaller range of date/time it can handle ==> 1970 to 2039, where DateTime and Date can handle from 1000 to 9999. Date is nice if you do not need time and takes up less space in the table. I use TimeStamp most of the time. I am information hungry so I like to know exactly when something happens, and sort of lazy by not creating full inserts.

rcoursey

What is necessary to get timestamps to automatically insert or update during a query?

Do the different type of queries require a different approach to getting the automatic timestamps?

What is the best way to handle adding or subtracting time or dates?

mnewhart

Here is my cart sql that creates a timestamp field:

CREATE TABLE carts (
cuid int(10) NOT NULL auto_increment,
cartid varchar(48) NOT NULL default '',
itemid varchar(50) NOT NULL default '',
cotm tinyint(1) unsigned NOT NULL default '0',
itemqty tinyint(3) unsigned zerofill default NULL,
itemamount decimal(9,2) default NULL,
itemdesc varchar(50) NOT NULL default '',
itemscent varchar(50) NOT NULL default '',
memberid varchar(48) default NULL,
referaffil varchar(20) default NULL,
cartrecdate timestamp(14) NOT NULL,
PRIMARY KEY (cuid)
) TYPE=MyISAM;

Notice the cartrecdate is a timestamp... the 14 does a full stamp in the format YYYYMMDDHHMMSS. You can also use 8 which is just the date part. There is nothing else you have to do on an insert.

$mysql = "INSERT INTO carts (cuid, cartid, itemid) VALUES (0, 12, 144)";

Notice I did not include the timestamp field. The record would contain a date/time of NOW() in the format stated above. Also, my cuid field would not have a value of zero, but the next number in sequence because it is an auto-increment field... As for the different query types - SELECT, INSERT, UPDATE - you can just refer to the field: "SELECT cartrecdate FROM carts". It is the format that you want to display it that counts... You can accomplish this in MySQL or PHP. I like to draw the raw contents from MySQL and format it in PHP, or draw both.

I would suggest getting the MySQL Manual at: http://www.mysql.com/documentation/index.html

Look for DATE_ADD, DATE_SUB, and DATE_FORMAT to work with dates (search the manual)...

Here is an example of a query using DATA_ADD:

  # Select Wholesalerequests 4 hours old
  $thissql = 'SELECT * FROM `wholesalecustomers` WHERE requestdate >= Date_Add( NOW( ) , INTERVAL - 5 HOUR ) AND requestdate < Date_Add( NOW( ) , INTERVAL - 4 HOUR )';  

  $result = mysql_query($thissql);

Hope this helps...