SELECTing dates that are in a different format

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

Moderator: General Moderators

User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECTing dates that are in a different format

Post by Eran »

Oh by the way the speed difference is only tangible when using millions of rows (i.e: stat tracking).
Did you actually benchmark this? or are you going by instinct (I vote for the latter). Show some repeatable benchmarks.

I've worked / am working with databases several dozen GBs in size and storing up to hundreds of millions of rows (per table). I can assure you that using MySQL timestamps is the least of my concern in those databases. Switching it to integers would not make anything faster in any noticeable way, but most surely complicated many processes. As I mentioned previously, such optimizations at the cost of portability and simplicity are the very definition of premature optimization.
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post by xjake88x »

If you're asking did I use some kind of benchmarking software or "official" script, all I did was a simple microtime before & after difference and ran a for loop for like 50 queries or so:

Code: Select all

 
$start = microtime();
for($i=0;$i<50;$i++)
{
    //select all, ordered by timestamp desc
}
$finish = microtime();
$time = $finish-$start;
 
 
 
 
$start = microtime();
for($i=0;$i<50;$i++)
{
    //select all, ordered by datetime desc
}
$finish = microtime();
$time = $finish-$start;
 

The difference in time between method 1 method 2 was only a small percentage but it was consistent. Try it yourself before making unbecoming accusations :/
If you haven't noticed that difference before, chances are you have your database configured to favor datetime.


pytrin wrote: most surely complicated many processes. As I mentioned previously, such optimizations at the cost of portability and simplicity are the very definition of premature optimization.
By the way, I don't know why you keep saying this. I think you do good work but this doesn't make any sense.

Complicated? You're a programmer, how can the simple idea of a timestamp be complicated to you? It is one number that represents an exact moment in time.
Portability? Because an industry-wide standard format isn't portable?

I won't comment on the fact that premature optimization has nothing to do with using a well supported format. All I can say is you must be prematurely optimizing your ego.

I never said don't use DateTime, but you are trying to claim that "Apples are the best type of fruit, only eat apples, everything else is wrong." Clearly, apples and oranges are just different. Just because you like one thing shouldn't mean that you can come in with malicious attempts to demerit a highly established format.
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post by xjake88x »

From mysql.com
DATETIME takes 8 bytes of storage.
INT takes 4
TIMESTAMP takes 4
DATE takes 3

DATETIME and TIMESTAMP have lots of useful functions.

TIMESTAMP is stored as GMT and TZ-converted on input/output.
DATETIME -- think of it as a snapshot of the clock on the wall (no timezone, daylight savings time, etc)

The diff of two TIMESTAMPs is a true elapsed time.
The diff of two DATETIMEs can be off by an hour due to DST, or more if data comes from multiple timezones.
The diff of two INTs depends on where the INT comes from (localtime, gmttime, etc).

5 = 4 + 1 -- The extra 1 is because it is NULLable, not NOT NULL. This is usually inappropriate for a time field.

DATETIME -- Obvious what the value represents.
INT -- smaller
TIMESTAMP -- has both advantages, so I lean toward it.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECTing dates that are in a different format

Post by Eran »

I'm just trying to point out better practices, not personal preferences. I've iterated the reasons a native type is more portable / useful in contrast to your declaration that an INT is the preferred way to store dates/times in a database, to which you gave no supporting reasons outside of minor speed gains. That is the definition of premature optimization. Use what you want, I don't really care, just don't misguide beginners with your dogma.
Weirdan wrote:There's nothing easy about working with times and calendars, despite some people thinking the opposite.
Maybe it's not just my personal preference, just a thought.
All I can say is you must be prematurely optimizing your ego
I don't think further discussion will benefit anyone. Peace out
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: SELECTing dates that are in a different format

Post by JakeJ »

I don't think further discussion will benefit anyone. Peace out
Oh come on. What's wrong with a little levity?

SELECT * FROM personalities WHERE humor > 0;
NO RECORDS RETURNED
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post by xjake88x »

Yeah man, dogma is the most inarticulate choice of words I could imagine. With all due respect it doesn't make much sense.

I develop by best practices. IMO you are pushing your preferred method which isn't the absolute solution to every situation. Some things come down to the context. Working with dates is already a minor and pretty negligible aspect of most systems so it's not like there is going to be some huuuge benefit in using one over the other. Relatively speaking, there are not multifarious operations being performed with dates. It pretty much comes down to speed. Simplicity is not an issue because working with dates, in any format, is extremely simple. It's absolutely for beginners, and nobody should have a problem with it. I understand you are proud of your abilities as a capable developer but we both know you're not the only one here who knows their way around manifold programming languages. IMO I think you made a mountain out of a mole hill :P it's alright though haha
Post Reply