Getting Distinct Date from mysql db with timestamp and around half million records

Maybe there’s something I’m missing?

<hr>

Rudy, you’re telling me that


WHERE orderDate >= UNIX_TIMESTAMP(CURRENT_DATE)
   AND orderDate  < UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 8 DAY)

and


WHERE orderDate >= UNIX_TIMESTAMP(CURRENT_DATE)
   AND orderDate  <= UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 7 DAY)

are not the same thing?

<hr>

And you Jake, you’re telling me that by your understanding UNIX_TIMESTAMP of a day always returns 12am in that day? Beside a bad understanding of what “12am” means, what’s the point for TIME in TIMESTAMP then?

<hr>

Pardon me, but I see some serious serious logic issues here.

We’re talking about days as integers. For integers, (a <= b < c) is, in fact, equivalent to (a<= b =< c-1). (a<= b =< c-1) being BETWEEN.

If by “12am” you mean midnight (24:00), then, if 00:00:00 exists for a day, then 24:00:00 doesn’t exist. It’s 00:00:00 for the next day. I believe someone needs to learn to read the clock a little bit better. :wink: Anything after “12am that day” is actually in the next day.

12am is the beginning of the day, not the end. Which is why it finishes at 23.59 and starts at 00:00. Hence 1 hour after 12am on the 3rd june is 1am on the 3rd of june, but 1 hour before it is 11pm on the 2nd of june. Hence the following is invalid:

Anything after “12am that day” is actually in the next day.

UNIX_TIMESTAMP does return 12am if you only pass a date to it. This will clear things up for you:

SELECT FROM_UNIXTIME( UNIX_TIMESTAMP( CURRENT_DATE ) )

If you were passing a time to it as well as the date, it would of course reflect the time.

Hence:


WHERE orderDate >= UNIX_TIMESTAMP(CURRENT_DATE)
   AND orderDate  < UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 8 DAY)

And


WHERE orderDate >= UNIX_TIMESTAMP(CURRENT_DATE)
   AND orderDate  <= UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 7 DAY)

are different by 23 hrs, 59 minutes and 59 seconds.

they most definitely are not the same thing

let’s take CURRENT_DATE to be 2012-06-05

then this –


 WHERE orderDate >= UNIX_TIMESTAMP(CURRENT_DATE)
   AND orderDate  < UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 8 DAY)

translates to this –


 WHERE orderDate >= 1338868800
   AND orderDate  < 1339560000

meanwhile, this –


 WHERE orderDate >= UNIX_TIMESTAMP(CURRENT_DATE)
   AND orderDate <= UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 7 DAY)

translates to this –


 WHERE orderDate >= 1338868800
   AND orderDate <= 1339473600

i shall leave it to you to understand why < 1339560000 is not the same as <= 1339473600

[ insert horizontal rule here ]

i believe someone needs to learn the UNIX_TIMESTAMP function a little bit better :wink:

Awww noes, I ruineded it :confused:

Off Topic:

If the table was travelling at 0.999994212 times the speed of light relative to the database engine, then 1 second in the table’s frame of reference would be 23 hrs, 59 minutes and 59 seconds in the database engine’s frame of reference, and as such would make the two clauses identical :lol:

You can say that a few more times. I’ve told you I don’t do much MySQL. And from what little I’ve read with this occasion, it seems TIMESTAMP is not the way to go for the purpose OP intended. How come no expert pointed that out? :slight_smile:

<hr>

So, Rudy, given the above, let me understand, your solution to “the next 7 days from today” is to return orders from noon in today to noon in the 8th day? If that, than WOW!!!

<hr>

But no, because Jack is wrong.

Or maybe for you Jake? http://i1054.photobucket.com/albums/s490/itmitica/itmitica-jake-is-wrong-timestamp.png

I’m still not sure if by “12am” you really mean to say “00:00:00”?

Off Topic:

And Jack, I said this:

If by “12am” you mean midnight (24:00) […] then 24:00:00 doesn’t exist. It’s 00:00:00 for the next day.

I wondered why someone would try and bring a casual “12am” into a datetime discussion. :wink: That’s why I felt it necessary to clarify the 00:00:00.

<hr>

I still say they are the same thing WHEN TALKING ABOUT THE DAY PART. Can you please post a more basic example, with only days? Remember, we’re talking about days ONLY, not datetime. :wink: Maybe you’re cheating a little bit here, Rudy?! :wink:

[QUOTE=itmitică;5131064]So your solution is to return orders from noon in today to noon in the 8th day?[/QUOTE]no, my solution is to return orders from midnight this morning (CURRENT_DATE) up to but not including midnight 8 days from today (CURRENT_DATE + INTERVAL 8 DAY)

did you not understand my explanation using the actual unix epoch integer values?

Well, according to Jake reading your query and his UNIX_TIMESTAMP interpretation, is actually “from noon this morning (CURRENT_DATE) up to but not including noon 8 days from today (CURRENT_DATE + INTERVAL 8 DAY)”. So yeah, I’m a little bit confused! :wink: LOL

<hr>

What say you about the other thing where you tried to fool me? Saying that when talking about only the day part:

i shall leave it to you to understand why < 1339560000 is not the same as <= 1339473600

when clearly they express the same day value!!!

WHICH is MORE THAN ENOUGH!. There is no need for the time value. THE DAY PART ALONE is ENOUGH. Is this a little thing you’re missing?!

[QUOTE=itmitică;5131079]What say you about the other thing where you tried to fool me? Saying that when talking about only the day part:

when clearly they express the same day value!!! [/QUOTE]i’m not trying to fool you, but rather, inform you

and they most certainly do not express the same day value

i think i should remind you of the old maxim about holes…

when you find yourself in a hole, stop digging!

clearly, you need to do some testing on unix timestamps to become more familiar with them

Well, according to Jake reading your query and his UNIX_TIMESTAMP interpretation, is actually “from noon this morning (CURRENT_DATE) up to but not including noon 8 days from today (CURRENT_DATE + INTERVAL 8 DAY)”. So yeah, I’m a little bit confused! LOL

12am is midnight, by all definitions I’ve ever seen or heard of. Of course, in other places it is different, however I stated my definitions for them above.

Also by my definitions, throwing insults and excuses after being corrected is rather ill mannered, and distracts the actual question behind the thread.

the only person talking about days here is you

this ~is~ about datetimes, specifically unix timestamps

much earlier in this thread, steve said

… the timestamps are not exactly the same as they will have ordered at different times during the day.

ID orderDate
2 1339166700
3 1339249500
4 1339164000
5 1339249500
6 1339254900
any further discussion in this thread of days rather than datetimes will be ignored by me

No holes here, sorry. And you know the old maxim about emperor’s clothes, do you?


SELECT FROM_UNIXTIME( UNIX_TIMESTAMP( CURRENT_DATE + INTERVAL 8 DAY ) )
UNION
SELECT FROM_UNIXTIME( UNIX_TIMESTAMP( CURRENT_DATE + INTERVAL 7 DAY ) ) 

FROM_UNIXTIME( UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 8 DAY) )
---------------------------------------------------------------------------------------
2012-06-[B][SIZE=3]13[/SIZE][/B] 00:00:00
2012-06-[B][SIZE=3]12[/SIZE][/B] 00:00:00

Clear as daylight. No need to play the “can’t see the obvious point” game.

<hr>

Which leads me to the better answer: for this deed the OP should use/convert to orderDate to date type (what he should have done in the first place since timestamps have their use but no here), which leads to unambiguous, clear and standard selects:


select orderDate
from tbl
where orderDate between CURRENT_DATE and DATE( CURRENT_DATE + 6 )   --or +7 or +8, whatever
order by orderDate asc

It can be done several ways, each of them with keeping with the original table or not.

Instead of trying to approximate timestamps to day’s end and beginning, extract the day part from timestamp values. The clear way and cross-RDBMS way of doing it. No “00:00:00”, "23:59:, “12am”, UNIX_TIMESTAMP crap. It has no place in this case, on orderDate. My initial point before I unfortunately digressed. And what experts should have recommended.


SELECT CURRENT_DATE
UNION
SELECT DATE( CURRENT_DATE + 6 ) 

CURRENT_DATE
-------------------
2012-06-05
2012-06-11

7 days total.

you totally missed the whole point of this thread, which is that the orders have been logged into the database with a timestamp

saying that the orders should be recorded with date only (“timestamps have their use but no here”) is absolute nonsense and reveals that you know very little about real world order applications

absolute nonsense

please, i beg you, you’re in a deep enough hole… stop digging

you’re making yourself look silly

Post #11:

[QUOTE=itmitică;5130411]select id, date(orderDate) as day
from tbl
where date(orderDate) between date(now()) and date(now()) + 6
order by date(orderDate) asc[/QUOTE]

I believe I haven’t. I maybe have some syntax problem in there, by my #31 post redeems it.

What you’re “missing”: I provided and alternative approach. Far more sane, using the date type. An approach which also happens to be cross RDBMS valid.

About the fact that I “know very little about real world order applications”. See above. Need I spell it for you? OK. What if you go from MySQL to PostgreSQL? UNIX_TIMESTAMP bad. Standard SQL good. Clear enough?

Plus, beside the fact that the timestamp type is limited as range, I trust that you can figure out more reasons why date type is better for the OP on the orderDate.

Now, you can still play the “hole” and “silly” cards. Make them a Solitaire, will you? Thanks.

[QUOTE=itmitică;5131192]Far more sane, using the date type.[/QUOTE]nonsense, absolute nonsense

I’d like to chime in here.

  1. In MySQL, if you apply a function to a column value in the WHERE clause it cannot use the index for that query.
  2. In MySQL, if you apply a function to a column value in the WHERE clause it cannot use the index for that query.

Indeed, that the same point twice. For emphasis don’t you know.

So this …


select orderDate
from tbl
where orderDate between CURRENT_DATE and DATE( CURRENT_DATE + 6 )   --or +7 or +8, whatever
order by orderDate asc

… forces MySQL to do a full table scan. On a table with 500k rows. Might not be the best idea in the world.

And unlike Oracle, MySQL does not have function based indexes, so that’s not an option either.

So, if life gives you lemons, make lemonade. Or if life gives you timestamps, make timestamp’onade. Or something.

Okay, so the difference between 7 and 8


1st 12:00    2nd 12:00    3rd 12:00    4th 12:00    5th 12:00    6th 12:00    7th 12:00    8th 12:00    9th 12:00
.............1............2............3............4............5............6............7????????????


1st 12:00    2nd 12:00    3rd 12:00    4th 12:00    5th 12:00    6th 12:00    7th 12:00    8th 12:00    9th 12:00
.............1............2............3............4............5............6............7............8????????

Since we’re using timestamps, and thus date and time, we can start at any point of the day. Suppose we start the 1st at 12:00 (24h notation). Then if we add 7 days we end up 12:00 on the 8th. So we miss all the orders beween 12:00 and 0:00 on the 9th. i.e., what we’re showing the user of the system is not complete.
If however we change the 7 to 8 we get too much information, that is, we also get from 0:00 until 12:00 on the 9th. But that’s alright since the beauty of too much information is that you can ignore the part that you don’t like.
So first you request the data for 8 days from MySQL, and then in PHP you ignore the data from that part of the last day you don’t actually need.

That being said, I wonder if the OP couldn’t just use


// PHP <= 5.3
$start = mktime(0, 0, 0); // timestamp for today 0:00
$end = mktime(0, 0, 0, date('n'), date('j') + 7, date('Y')); // timestamp of the date 7 days from now, 0:00
$query = "SELECT order, data, stuff FROM daTable where orderdate >= $start and orderdate <= $end";

// PHP >= 5.3 alternative: 
$start = new \\DateTime()->modify('midnight')->getTimestamp();
$end = new \\DateTime()->modify('+7 day, midnight')->getTimestamp();
$query = "SELECT order, data, stuff FROM daTable where orderdate >= $start and orderdate <= $end";
// \\DateTime rocks!

That should take care of the time problem just nicely.

Obviously, it would be true if there were indeed A FUNCTION APPLIED TO THE orderDate COLUMN!!! How about that for emphasis?!

  1. In MySQL, orderDate is a column without a function applied to it.
  2. In MySQL, orderDate is a column without a function applied to it.
  3. In MySQL, orderDate is a column without a function applied to it.
  4. In MySQL, date(orderDate) is a column with a function applied to it.

<hr>

And guess what I’m saying in post #9:

[QUOTE=itmitică;5130381]
I’d also consider this:

If you can afford it, consider making a temp table:

create tbl_orders_ahead
as
select [whatever]
from tbl
where orderDate between date(now()) and date(now()) + 6

It’ll be faster to get reports from the temp table then the full table, when using functions in your query.[/QUOTE]

<hr>

OP got him self into trouble using UNIX_TIMESTAMP on a column where obviously it should have use a date type.

My better advice is: if life gives you standard SQL you should use standard SQL. Date spells out date type. UNIX_TIMESTAMP spells out compatibility trouble. So make some compatibilemonade. :wink:

Ahum. I did not say that. Can I take it back? :shifty: (point is still valid, but doesn’t apply to this scenario – read the query wrongly. To quote @dklynn;, the fast I go, the hurrier I get).

How about the rest of my post though?

standard sql also provides for INTEGER

the unix timestamps stored in the orders table are integers

so, as the kids say, vwalah!!!

now, if you want to write a query that’s compatible across all database systems, just use integers

an example of this is shown in post #35, where the boundary points of the range are supplied by php

using mysql’s built-in functions UNIX_TIMESTAMP and CURRENT_DATE is not essential, i just find it a lot simpler and clearer as to intent

what ~is~ mandatory is an understanding of exactly which integer values are to be retrieved, so that you can set up the bounds of the range in the WHERE clause

so once again, i shall leave it to you to understand why < 1339560000 is not the same as <= 1339473600

:smiley:

Sounds like a good idea, calculating the low value and high value for UNIX_TIMESTAMP outside the SQL.

It still leaves us with a compatibility problem, which, of course, we should FIX IT! I mean, I can figure out a way to express all types in integer values, not just datetime. But where would that leave us?

From a standard point of view, a standard date type for a orderDate column is the way to go.

<hr>

I shall leave to others to understand why the title of this thread “Getting Distinct Date” is not the same with “Getting Distinct UNIX_TIMESTAMP”:

Sat 3rd June
Order 1
Order 2
Order 3

Sun 4th June
Order 4
Order 5
Order 6

What the OP asks. :smiley:

<hr>

…and why 1339016399 is the same with 1338930000, when it comes to the day part:


SELECT UNIX_TIMESTAMP( '2012-06-06 00:00:00' )
UNION
SELECT UNIX_TIMESTAMP( '2012-06-06 23:59:59' ) 

UNIX_TIMESTAMP('2012-06-06 00:00:00')
--------------------------------------------------
1338930000
1339016399

Date spells out date type. UNIX_TIMESTAMP spells out compatibility trouble

If, of course, the OP wanted to migrate at any time, they would possibly have that problem. Though I’m not a follower of the ‘use generic SQL all the time just in case’ ideology, because of course different databases are used because of their own benefits. In a large enough application, queries would be customised for their database type with different plugins (and I’ve seen some frankly hilarious attempts at getting the same effect with an XML database before now, including one which attempted parsing from standard sql… The dodgy nested-query and inner-join implementations of which almost sent me cross-eyed). I’ve used MySQL, PostGreSQL, Oracle, SQLServer, SQLite and IBM’s DB2 - But in personal projects and full scale client projects which I’m in control of, it’s always MySQL for me.

However, a date is not a standard variable type whereas an integer is. Every C-based language (AFAIAA) natively treats a given point in time as an integer - seconds since 1 Jan, 1970, so it would make sense for PHP to be given that variable type by MySQL instead of having to convert from a datetime.

But it seems I am comparing apples with oranges.

What you’re “missing”: I provided and alternative approach. Far more sane, using the date type. An approach which also happens to be cross RDBMS valid.

About the fact that I “know very little about real world order applications”. See above. Need I spell it for you? OK. What if you go from MySQL to PostgreSQL? UNIX_TIMESTAMP bad. Standard SQL good. Clear enough?

What you’re missing is the title of this thread - it states MySQL :wink:

I don’t think it’s possible for anyone to have as much experience with this stuff as Rudy. He wrote the book on it - literally. And the logo on the top of said book is Sitepoint’s very own. A flame war with him is not going to get you very far around these parts :wink: