What to use when inserting time/dates + how to select if dat
Posted: Thu Jan 04, 2007 4:34 am
Hello,
When I've asked around people tell me that I shouldn't store my dates and so on in varchar and that MySQL can insert them for me..
Here is how I insert the dates and how I remove the rows that are older than five days:
And here is how the database structure looks like:
I want to store the time and date in the database and be able to use the information to for example remove rows that are older than five days. It seams like CURRENT_DATE or NOW() can be used to insert the infromation without PHP into the tables and that I should use something like TIMESTAMP or DATETIME in the table in the database.
The reason for that I store both the timestamp and the date in the databae for the moment is that I need to remove rows that are older than five days (as in the code above) and that I need to be able to select all rows with a date between three and five days ago. What kind of MySQL info should I have and how can I do the things I want with MySQL?
Also, I'm currently storing prices in varchar but someone told me that I should use DOUBLE or DECIMAL or something similar, which one should I use for prices (ex. 2.95)? And is there any difference between storing numbers in INT or varchar?
Best Regards
Oskar R
When I've asked around people tell me that I shouldn't store my dates and so on in varchar and that MySQL can insert them for me..
Here is how I insert the dates and how I remove the rows that are older than five days:
Code: Select all
// The current time
$date = date("H:i:s M j, Y");
// The current unix timestamp
$timestamp = time();
// See if there is any row in the temporary database that is older than
$cutoff = time() - 432000; // 5 days ago in seconds
mysql_query("DELETE FROM temp_users WHERE script_received < '$cutoff'");
// Set the latest login in the user table
$sql8="UPDATE $user_tbl SET (latest_login_date, latest_login_timestamp) VALUES ($date, $timestamp) where user_id = ".$vuserid[0]."";
$result8=mysql_query($sql8);Code: Select all
CREATE TABLE `login_logs` (
`login_id` int(11) NOT NULL auto_increment,
`user_id` int(10) NOT NULL default '0',
`ip` varchar(30) NOT NULL default '',
`logged_date` varchar(30) NOT NULL default '',
`logged_timestamp` varchar(30) NOT NULL default '',
PRIMARY KEY (`login_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;The reason for that I store both the timestamp and the date in the databae for the moment is that I need to remove rows that are older than five days (as in the code above) and that I need to be able to select all rows with a date between three and five days ago. What kind of MySQL info should I have and how can I do the things I want with MySQL?
Also, I'm currently storing prices in varchar but someone told me that I should use DOUBLE or DECIMAL or something similar, which one should I use for prices (ex. 2.95)? And is there any difference between storing numbers in INT or varchar?
Best Regards
Oskar R