[SOLVED] What to use when inserting time/dates + how to select if date older than 5 days

[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=php:0]// 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]

And here is how the database structure looks like:
[CODE]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 ;[/CODE]

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

[hvle]

I would recommend a few optimization for your code:

1. you have varchar(30) as datatype for 'logged_timestamp'.  Bad Idea!!! mysql will treat this like a string of digits, not a number as a whole, therefore, comparing and evaluate this value is extremely slow compare to integer. 
You should change the data type to INT(11).

2.  logged_date is a redundant.  You do not need this type since you already have the logged_timestamp
if you like to convert this timestamp to a date format, use: date("H:i:s M j, Y", $timestamp)

3.  The way you cut off rows with 5 days old or more is correct.  However, for this to work properly, you must change the datatype for timestamp to INT.

Cheers,

[fenway]

[quote author=hvle link=topic=120965.msg496740#msg496740 date=1167907761]
3.  The way you cut off rows with 5 days old or more is correct.  However, for this to work properly, you must change the datatype for timestamp to INT.
[/quote]

Actually, you should leave it as a datetime column, and then simply query on "WHERE logged_timestamp < NOW() - INTERVAL 5 DAY".

[hvle]

I prefer to have timestamp as unix timestamp because it is universal.  That way I can manage the time better with PHP or other scripting language.

[obsidian]

[quote author=hvle link=topic=120965.msg497423#msg497423 date=1168005235]
I prefer to have timestamp as unix timestamp because it is universal.  That way I can manage the time better with PHP or other scripting language.
[/quote]

When you're working [i]within[/i] a structure, though, you're much better off to use that structure's format of time. Besides, PHP and other scripting languages have functions to deal with dates and times in any format you want to send it in. Also, if you're wanting to send the data to PHP via a unix timestamp, you can always change it to that when you select it. If you'll follow the intended format, and that which fenway suggests, you'll be able to do actual calculations [i]within MySQL[/i] on your times to save yourself a ton of work and page processing time, too.

[fenway]

[quote author=hvle link=topic=120965.msg497423#msg497423 date=1168005235]
I prefer to have timestamp as unix timestamp because it is universal.  That way I can manage the time better with PHP or other scripting language.
[/quote]
Like obsidian said, that's missing the whole point.

[toplay]

[quote author=hvle link=topic=120965.msg497423#msg497423 date=1168005235]
I prefer to have timestamp as unix timestamp because it is universal.  That way I can manage the time better with PHP or other scripting language.
[/quote]
I prefer DATETIME field over timestamp. You have to remember that when you do an UPDATE and the table has a timestamp column, MySQL will automatically update that column with the current date and time (even when the timestamp column is not specified in the UPDATE). This may not be what you want or expect, and prefer to keep the contents of the date and time already there.

Tip: When you want to keep the contents of a timestamp column the same during an update, do:

UPDATE `login_logs`
    SET
          `user_id` = 10
        , `logged_timestamp` = `logged_timestamp`  # preserves existing value
    WHERE
          `login_id` = 1
;

[hvle]

I don't know about this, is this a new feature?  Because it could totally messed up my data.  I would definitely don't want mysql to do any extra automatic thing to my data, and definitely don't want to write any more than I needed.

Perhap mysql can improved manipulation on other datatype, ie. string.  But i'm talking about an timestamp as an integer.  Think about how many ways you compares 2 integers.  In Binary, it's pretty much 1 way.

I would still prefer integer as timestamp despite of your 2 opinions.

to Toplay, I don't know what you said are true or not, but if it is, that'll be disaster to mysql.

[fenway]

That was an option they added for the first timestamp column, but I don't think it's the default action.

[hvle]

fenway, that's a relief, they should never make things like that as default.
After some deeper thought, I think both ways have it own benefit.  You can do more within mysql if you have datetime as the datatype, and i'm sure lot of lazy programmers (everybody) would love the automatic datetime updating feature.
I was being too defensive about my opinion, but you 2 gave absolutely good advices.

Thanks,