What to use when inserting time/dates + how to select if dat

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
oskare100
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

What to use when inserting time/dates + how to select if dat

Post by oskare100 »

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:

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);
And here is how the database structure looks like:

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 ;
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
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

You could use MySQL's date/time functions if you were to use a DATETIME column type, but I don't see anything wrong with what you're doing now.

You should certainly use INT instead of VARCHAR for integers. INT will store an unsigned integer up to ~4x10^9 in 4 bytes, whereas VARCHAR will store it in 11 bytes.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

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` TIMESTAMP NOT NULL default Now(),
  PRIMARY KEY  (`login_id`)
)
insert:

Code: Select all

$query = "INSERT INTO
		`login_logs` (`user_id`,`ip`)
	VALUES
		($uid, '$ip')";
delete old records:

Code: Select all

DELETE FROM
	`login_logs`
WHERE
	`logged_date` < CURDATE() - Interval 5 Day
Post Reply