Jump to content

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


oskare100

Recommended Posts

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
Link to comment
Share on other sites

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,
Link to comment
Share on other sites

[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".
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

[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
;
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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,
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.