Help & FAQs    Search BCF    Members    Groups    Events Calendar
Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


MySQL help

 Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

Phil.
World Chat Champion



Joined: 02 Feb 2002
Karma :
PostPosted: 22:27 - 28 May 2010    Post subject: MySQL help Reply with quote
Hey

I have a query with a few joins that pulls a bunch of data and a datetime field lets call it 'this_date'.

I am adding ORDER BY this_date DESC if a checkbox is selected.

This produces the orders in something like the following order:

2010-05-28 00:00:00
2010-05-27 00:00:00
0000-00-00 00:00:00
etc

However I need them ascending but starting with an actual date, rather than a few pages of '0000-00-00 00:00:00' then ascending dates.

Without having to rewrite a few scripts, is there any MySQL trickery to add some conditions to datetime ordering?

Thanks
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 23:49 - 28 May 2010    Post subject: Reply with quote
Hi

Do you want those rows with the empty dates listed at all or do you want to drop them? If you don't care about those rows then just use WHERE this_date > 0 (assuming it is a timestamp).

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

herulach
World Chat Champion



Joined: 19 Apr 2010
Karma :
PostPosted: 08:27 - 29 May 2010    Post subject: Reply with quote
What Keith said, or, if you need to display them, something like:

Code:
cast (case
when date > 0 then date
else "SOMETIME IN THE DISTANT FUTURE"
as datetime ) as 'SortingDate'


then order by your new field.

Depending on your table, it might be faster to do 2 queries, drop the 0s from the first one, then append a second query including only the zeros.
____________________
YBR 125>FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

Phil.
World Chat Champion



Joined: 02 Feb 2002
Karma :
PostPosted: 10:33 - 29 May 2010    Post subject: Reply with quote
Hi

Yes, I do need to pull all the records, including those with empty dates.

To help explain it here's a working query:

Code:

SELECT `orders_id` , `due_by_date` FROM orders WHERE `orders_status` = '2' ORDER BY `due_by_date`


The actual one is much bigger but this shows the problem. Due dates are set by the user (on a small number of orders). The default for 'due_by_date' field is '0000-00-00 00:00:00'.

I need to pull all the orders as in the query above, order them by due_by_date. The query above pulls about 500 rows, order by due_date desc will produce results like in my OP. This isn't ideal for the user, really they need to be ASC, but that puts all the results with '0000-00-00 00:00:00' first which is even worse.

So instead of:
2010-05-28 00:00:00
2010-05-27 00:00:00
0000-00-00 00:00:00

I need:
2010-05-27 00:00:00
2010-05-28 00:00:00
0000-00-00 00:00:00

Hope I have done a better job of explaining it this time.

Thanks for the suggestion Herulach, I'm not familiar with the syntax though, any chance you could amend the above with your suggestion and I will give it a run, see what impact it has on the speed of the query?

Thanks for the help as always.
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

supZ
World Chat Champion



Joined: 03 Feb 2009
Karma :
PostPosted: 16:11 - 29 May 2010    Post subject: Reply with quote
herulach wrote:
What Keith said, or, if you need to display them, something like:

Code:
cast (case
when date > 0 then date
else "SOMETIME IN THE DISTANT FUTURE"
as datetime ) as 'SortingDate'


then order by your new field.

Depending on your table, it might be faster to do 2 queries, drop the 0s from the first one, then append a second query including only the zeros.

i dont believe that would work.

at least in mssql it would whinge you're trying to convert a date to a string (in other words you're trying to order a date and string field type)


if it were me i think id add a silly date to it (like 01/01/4000) then use your code (im presuming you're displaying this in a webpage using php) to change it.

e.g.
Code:
select name, (case when duedate = '19000101' then '40000101' else duedate end) as 'showdate'
from testable
order by showdate


replace your field names and table names with the correct ones of course

then replace the 19000101 with your 0 date so what the query will do is, when it finds your 0 date it'll change it to 01/01/4000 (for display only)

dont mind the fact the dates are backwards. i always use ISO formatting when using dates in MSSQL so theres never any region confusion between date formats.

so in your code you'd want something along the lines of 'if the date = 01/01/4000 then display "date not set"'

havent done php in years so im not gonna try to do the syntax but hopefully you get the idea.

the idea behind this is by adding a silly large date to your 0 dates they'll always be at the bottom of your query and you can order them as you want.
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike
 Back to top
View user's profile Send private message You must be logged in to rate posts

Alexio
World Chat Champion



Joined: 27 Aug 2009
Karma :
PostPosted: 16:28 - 29 May 2010    Post subject: Reply with quote
Or if you're feeling really lazy you could always just execute two queries. One where date_time != '0000-00-00 00:00:00' and one where date_time = '0000-00-00 00:00:00'.

Now you have two data sets which you can just process one after the other, still have all of the data you required and everything in the order you wanted too.

Only a small bit of extra overhead performance Wink
____________________
will never give up his CG. I look at my fuel gauge more as a progress bar than a fuel gauge.
G: With my GSXR I do often effectively use it as a scooter with a clutch in town.
ms51ves3: why does it need 500 miles? Are you teaching it how to be a piston?
 Back to top
View user's profile Send private message You must be logged in to rate posts

Frost
World Chat Champion



Joined: 26 May 2004
Karma :
PostPosted: 17:25 - 29 May 2010    Post subject: Reply with quote
Performance isn't everything. Maintainability and clarity is everything. So many times i've seen someone try to do something really complex with a single 50 line quirey, yeah its quick, but i don't have a fucking clue how to change it to incorporate some new change. Laughing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Phil.
World Chat Champion



Joined: 02 Feb 2002
Karma :
PostPosted: 10:20 - 30 May 2010    Post subject: Reply with quote
Thanks for all the replies.

I thought maybe there would be a really simple way of having MySQL do what I needed, but it seems like the simplest solution seems to be duplicating the queries and array merge when they are done.

No big deal, seemed like an interesting problem and thought I might be able to learn something out of asking.

Thanks for all your time.
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

herulach
World Chat Champion



Joined: 19 Apr 2010
Karma :
PostPosted: 17:48 - 30 May 2010    Post subject: Reply with quote
supZ wrote:
herulach wrote:
What Keith said, or, if you need to display them, something like:

Code:
cast (case
when date > 0 then date
else "SOMETIME IN THE DISTANT FUTURE"
as datetime ) as 'SortingDate'


then order by your new field.

Depending on your table, it might be faster to do 2 queries, drop the 0s from the first one, then append a second query including only the zeros.

i dont believe that would work.

at least in mssql it would whinge you're trying to convert a date to a string (in other words you're trying to order a date and string field type)


if it were me i think id add a silly date to it (like 01/01/4000) then use your code (im presuming you're displaying this in a webpage using php) to change it.

e.g.
Code:
select name, (case when duedate = '19000101' then '40000101' else duedate end) as 'showdate'
from testable
order by showdate




Thats what I meant except I couldn't remember the proper date syntax. Date >0 should work though, if you just compare a date to a number it does it unformatted.

OP - Keith's code I've quoted above will work fine, and should be reasonably easy to understand afterward - just stick a comment if you're worried about someone else understanding it.

At the risk of sounding flippant, the best way to sort it is to require an input into the Due By date that isn't 1900. A test for the date being in the future at the time of input should be sufficient.

Depending on how your frontend will handle DB errors:

Code:
ALTER TABLE orders
ADD CHECK (due_by_date>NOW())


should work, but if you've no error handling it might balls it up.[/code]
____________________
YBR 125>FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

supZ
World Chat Champion



Joined: 03 Feb 2009
Karma :
PostPosted: 23:10 - 30 May 2010    Post subject: Reply with quote
herulach wrote:

At the risk of sounding flippant, the best way to sort it is to require an input into the Due By date that isn't 1900. A test for the date being in the future at the time of input should be sufficient.


good point, well made Smile
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike
 Back to top
View user's profile Send private message You must be logged in to rate posts

Alexio
World Chat Champion



Joined: 27 Aug 2009
Karma :
PostPosted: 04:15 - 31 May 2010    Post subject: Reply with quote
https://i8.photobucket.com/albums/a27/alsweetex/forum/mysqrl.jpg

Yeah, I guess there was no really simple way to do it in one query, but there are definitely ways it can be done. I'm sure you could just include some extra documentation / comments.
____________________
will never give up his CG. I look at my fuel gauge more as a progress bar than a fuel gauge.
G: With my GSXR I do often effectively use it as a scooter with a clutch in town.
ms51ves3: why does it need 500 miles? Are you teaching it how to be a piston?
 Back to top
View user's profile Send private message You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 13:00 - 01 Jun 2010    Post subject: Reply with quote
Hi

Most solutions are going to be lousy on performance as you will land up sorting on a derived field. Reading it into php and then sorting will also be lousy.

You could try this:-

Code:

(
SELECT *
FROM `DateSortTest`
WHERE `SomeDate` >0
ORDER BY SomeDate
)
UNION (
SELECT *
FROM `DateSortTest`
WHERE `SomeDate` =0
)


but not convinced that MySQL will maintain the sort order always.

Personally I would be tempted to have a flag to say that the date field has meaningful data in it for that row. Say "PlannedEvent" as a boolean:-

Code:

SELECT *
FROM `DateSortTest`
ORDER BY PlannedEvent, SomeDate


Not strictly good practice (as you are effectively duplicating data) but in this case I wouldn't worry. And you can always use a trigger to force update the flag field is the date field changes.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

supZ
World Chat Champion



Joined: 03 Feb 2009
Karma :
PostPosted: 14:44 - 01 Jun 2010    Post subject: Reply with quote
Kickstart wrote:
but not convinced that MySQL will maintain the sort order always.

wouldnt have thought it does, MSSQL certainly doesnt.

tried a union myself Wink
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike
 Back to top
View user's profile Send private message You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 15:00 - 01 Jun 2010    Post subject: Reply with quote
supZ wrote:

wouldnt have thought it does, MSSQL certainly doesnt.


Playing MySQL appears to, but I wouldn't be surprised if it isn't guaranteed to.

Sorting after that would mean using derived fields and would likely be slower, but could be done. Doubt it would be any different performance wise than you CASE suggestion:-

Code:

SELECT *
FROM ((
SELECT Id, SomeDate, '0' AS UseDate
FROM `DateSortTest`
WHERE `SomeDate` >0
)
UNION (
SELECT Id, SomeDate, '1' AS UseDate
FROM `DateSortTest`
WHERE `SomeDate` =0
))
ORDER BY UseDate, SomeDate


All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts
Old Thread Alert!

The last post was made 16 years, 100 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 
Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.09 Sec - Server Load: 0.96 - MySQL Queries: 13 - Page Size: 91.62 Kb