Tenths or greater of a second is pretty much pointless. You can count on full seconds the exact same number on fractions of a second without ever needing to worry about any type of data conversion. Ie:
Should yield the same records regardless of if there is a millisecond or not on it.Code:
WHERE created BETWEEN '2013-08-17 11:30:00' AND '2013-08-17 11:30:01'
So again, I ask, why are you concerning yourself with milliseconds? This is like telling me that its -15.889 degrees outside right now.
As I said in Post #11, when I look at the "visitor_log" table, I want greater precision so I can see exactly when someone lands on a particular profile.
Because if the same person visits DoubleDee's 10 times in the same second, then that is a red flag to me that something is going on.
If I did not have precision beyond a second, then I would just see 10 entries in my "visitor_log" table that looked like this...
Instead of giving me slightly more information like this would...
Do I need this much precision?
But does building it in now hurt anything?
It never hurts to err on the side of having too much information or too much precision...
I'm going to duck after saying this but having the milliseconds really does not give you any more information. Either way you know that someone hit a profile x number of times in a second.
I'm actually not quite sure why that is or would be an issue in the first place but I'm sure you'll tell me :)
Does it give me tons more info? No.
Might it help to know if two hits to a Profile were 0.99 seconds apart (e.g. simple screen refresh) versus 0.05 seconds apart (e.g. someone slamming a profile)? Possibly if you had a lot of hits.
I'll go back to...
"Does it do any great harm by having the extra precision?"
Because I have never built or run a website before, it seems like having as many tools and data and precision at my disposal would be a good thing more times than not?!
I guess I'm curious why you guys need so much "proof" from me today on something that seems so insignificant?? :confused:
(It's not like I'm not validating URL data or storing passwords in plain-text?!)
Floats on the other hand would be 8/23 (32bit) and 11/52 (64-bit double). Also, don't forget that as soon as you are calculating floats, you will be tied into standard precision loss resulting in unusual results even when they *appear* to be integers.
Just to give you an idea, without going into actual computation mathematics here, here is a very simple example:
Hmmm, k so that's not quite the 0.3 it should be:PHP Code:
$f = 0.1 + 0.2;
That long one is actually carried over with every calculation you do, despite appearing to only be 0.3. Best part? PHP will attempt to round it to 0.3 in output, so almost every output will say 0.3 making it much more difficult to determine a loss. Eventually you will hit a precision problem during calculations.Code:
The only thing I'm saying is that you potentially will have a headache for little to no gain. As it sits right now, you cannot properly deal with the numbers on a 32-bit machine (although in fairness I think that the float is 64-bit even in an x86, but I'll compile a x86 to see what it does to verify when I get home). Its simply so much easier and more reliable to stick with a DateTime type in SQL and PHP, or integers than to deal with floats and parts. There becomes a point where storing a certain level of precision is ludicrous, and this is one of those spots. Unless you have a reason to search on fractions of a second, than there is NO reason to go beyond a one second range. You can still COUNT your records to find the number of attempts within a 1 second range if you want.
But where am I using Floats??
The whole reason I made my "created_on" field decimal(17, 3) was to avoid what you are talking about.
So I thought I already addressed this? :confused:
(Like I said, I always try to *over-compensate* when I program because I know that I will under-estimate my need for something later.)
If you can indeed show me that I'm working with Floats and not "decimal(17, 3)" then it comes down to...
Do I have time to fix this?
(You may laugh, BUT, I have other things I am busy tweaking, AND, the last thing I need is to make some "simple" change and then introduce a new series of bugs because I didn't test well enough.)
I appreciate you playing Devil's Advocate here, and challenging me to code "smarter", but at some point things have to wait for v3.0...
Time will tell if that applies here.
Most languages deal with float/double, not with a decimal, so yes, you are always dealing with a float (or since you've actually used a string, you are dealing with a string which can be interpreted as a float) in PHP. PHP has no primitive for a decimal datatype. It doesn't matter that SQL can get away with shifting bits in a datatype, it doesn't change that PHP cannot interpret it in any way other than a float.
Which reminds me though, the x86 build uses a 64bit float.
1.) I don't see where the Float vs Decimal becomes an issue here. (I'm not doing math, just storing a Timestamp to 3 places past the Second spot.)
2.) It looks like the reason I made this design decision was *not* what I first thought. I choice the extra precision because I have a Unique Key on...
And if I just used DateTime for created_on, then I'd most certainly have collisions...Code:
member_viewed_id + visitor_id + created_on
Have another workaround?
3.) The best I can do is on my new MBP which will have MAMP v2.2 with MySQL 5.5.33
If you have any ideas of how to leverage something there that I don't have here on my old MacBook and MAMP v____ with MySQL v5.0.41, then speak up.
That sounds like a normalization issue to me then. If the records are duplicates, why do you want to keep them in the first place? Just reject it. If you have multiple entries within a single second, I'd suggest that there is likely a problem with the code itself; most http requests will probably run in excess of a second for an entire round trip.
Or, if once a primary key is determined, than if the composite is too cumbersome use a surrogate key.
This *looks* like its for determining if a user has viewed a members profile. Is there a need to even store a date here at all? If its not used for anything, than perhaps simply the fact that a visitor has viewed the profile is sufficient.
My Data Model is ROCK SOLID.
And since you can easily visit someone's profile multiple times in under a second, I originally wanted the delineation by a more precise timestamp. (I can wrap on my F5 key and have an enormous amount of "hits" on a given Member's Profile in under 1 second.
Could I remove my Unique Key/Index on the three fields mentioned above and just rely on my Surrogate PK for uniqueness?
But do I also prefer a *physical* way to identify each record's uniqueness?
It is my database design style to always try and have a *physical* way to identify a record. (It's called a "Natural Key".)
I tend to rely on Surrogate Key, HOWEVER, I usually create Unique Indexes on one or more Fields to ensure that uniqueness is a *physical* thing.
Because I find tables that *just* rely on Surrogate Keys to be dumbass, because they do NOT prevent this...
While this is less crucial on a "log" table, I still like a way to PHYSICALLY - have I used that word before? - distinguish records...
Again, this is the VISITOR_LOG table, and it logs *all* activity of Members and Anonymous Visitors visiting someone's Member Profile.
With that said, because I *also* log Anonymous Visitors, having the extra precision on the Timestamp makes even more sense...
Answer: your date is a string, treat it as such.
Also, you can add a counter to a primary record and update that on conflict. Done and done.