[SOLVED] Question about the "date / time" continuum - timestamp or add date as varchar?

[greencoin]

I have a new project that consists of work order tracking. It's nice cause I can use a lot of the same code from my last project which was delivery tracking. However when I tried to create a MySQL table using two timestamp fields, it stopped me. MySQL will only allow one timestamp.

 

So I did some googling and now am thinking I can use the php date function to get the date format of my choice and post the results into a textfield then post to MySQL as a varchar. I'll still be able to sort date records the same in my queries and it's easier for me as a n00b to manipulate the data; Ditching the time and creating expressions / queries on timestamp fields gives me fits.

 

Does anyone see any reason why I shouldn't use varchar for dates? Thanks ~Rich

 

** update - just thought about it... if I save as "varchar" then I can't compare dates like I did in my previous project, can I?

 <?php
if (isset($_POST['min']) && isset($_POST['max'])) { // where min and max are the names of the two form fields
    $sql = "SELECT * FROM GC_Tracker WHERE time >= '{$_POST['min']}' && time <= '{$_POST['max']}'";?>

 

btw., I'm using two dates; a W/O created field and a W/O closeout field.

[Barand]

Only use type TIMESTAMP where you want the date/time to be automatically updated if the record is updated. (ie timestamped as it says on the tin)

 

Otherwise use

 

DATE - (format yyyy-mm-dd) if the time element is not required

DATETIME (format yyyy-mm-dd hh-ii-ss) if you need the time element

INT and store the unix time value

 

Don't format data in your preferred format before storing, always format on output.

 

If you store it to look pretty as say, 25th Dec 2007, it will be as much use as a chocolate teapot when it comes to sorting, comparing dates etc.

 

[greencoin]

If my field is a date field and I pushed some numbers will it automatically try to fit the numbers to the date format or will it kick out an error?

 

??? ~Rich

[Barand]

When I ran the following, only the last was accepted

 

DROP TABLE IF EXISTS `test`.`dateformats`;
CREATE TABLE `dateformats` (
  `thedate` date NOT NULL,
  PRIMARY KEY  (`thedate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO dateformats VALUES
('25/12/2001'),
('12/25/2002'),
('25-DEC-2003'),
('25th Dec 2004'),
('2005-12-25');

[greencoin]

ok - thanks! You wouldn't happen to have a simple way to convert the data from "yyyy-mm-dd" to "mm-dd-yy" and vice versa would ya? Thanks again ~Rich

[Barand]

strtotime should handle both those formats

 

$mdy = date ('m-d-y', strtotime($dbdate));  // where dbdate is yyyy-mm-dd format

 

and back again

 

$dbdate = date('Y-m-d', strtotime($mdy));

[greencoin]

MANY MANY MANY Thanks!!! ~Rich

[Barand]

Correction to my previous post

 

strtotime() will recognise mm/dd/yy but NOT mm-dd-yy