If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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:

Code:

SELECT COUNT(*)
FROM Table
WHERE created BETWEEN '2013-08-17 11:30:00' AND '2013-08-17 11:30:01'

Should yield the same records regardless of if there is a millisecond or not on it.

So again, I ask, why are you concerning yourself with milliseconds? This is like telling me that its -15.889 degrees outside right now.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

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:

Code:

SELECT COUNT(*)
FROM Table
WHERE created BETWEEN '2013-08-17 11:30:00' AND '2013-08-17 11:30:01'

Should yield the same records regardless of if there is a millisecond or not on it.

So again, I ask, why are you concerning yourself with milliseconds? This is like telling me that its -15.889 degrees outside right now.

You kill me sometimes...

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.

Why?

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...

Yes, it does. Perhaps you're not aware, but by using floating point you actually LOSE precision. It is based completely on approximation and is why you often see lose in floating point calculations, even where you land on numbers. Integers are far more accurate to 100% precision; however, they are limited into a very small range; in PHP that is 32 signed bits or (-2147483648, 2147483647). 64bit builds won't suffer from this, but will be limited to a 64-bit max which is (-9223372036854775808, 9223372036854775807) if I'm not mistaken.
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:

PHP Code:

$f = 0.1 + 0.2;printf('%0.40f', $f);

Hmmm, k so that's not quite the 0.3 it should be:

Code:

0.3000000000000000444089209850062616169453

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.

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.

Last edited by Fou-Lu; 10-28-2013 at 11:58 PM.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

Yes, it does. Perhaps you're not aware, but by using floating point you actually LOSE precision.

Yes, I knew that.

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?

Originally Posted by Fou-Lu

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.

Okay, so I agree that maybe the extra precision that I believe I was recording in MySQL may be overkill.

(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...

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.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

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.

All valid points, but...

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...

Code:

member_viewed_id + visitor_id + created_on

And if I just used DateTime for created_on, then I'd most certainly have collisions...

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.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

If the records are duplicates, why do you want to keep them in the first place?

This is a *LOG* table. You store *everything* that happens in a log!

Originally Posted by Fou-Lu

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.

Think Denial-Of-Service...

Originally Posted by Fou-Lu

This *looks* like its for determining if a user has viewed a members profile.

Precisely.

Originally Posted by Fou-Lu

Is there a need to even store a date here at all?

Of course. I not only care that Fou-Lu visited DoubleDee's, but I care WHEN (and how often) this happens...

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?

Yes.

But do I also prefer a *physical* way to identify each record's uniqueness?

Yes!

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.

Why?

Because I find tables that *just* rely on Surrogate Keys to be dumbass, because they do NOT prevent this...