Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Mysql find closest to date

mysql date select

This topic has been archived. This means that you cannot reply to this topic.
11 replies to this topic

#1 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 19 August 2014 - 07:00 AM

Hello,

In mysql I have a table with a date field

I would like to find the rows closest to a date gived, but prioritizing the row in the past.

 

In other word, if I give the date 2014-06-01

 

And in my data, I have 3 rows: 2014-05-01, 2014-06-02, 2014-05-20

I would like to output 2014-05-20

 

But if I don't have anything before that date, I woule like to output the first one

For the same date gived: 2014-06-01

If I have thoses rows: 2014-07-02, 2014-08-02, 2014-08-20

I would like to output 2014-07-02

 

The only way I think of doing this is using some union, 2 select (one past, one future), then a other select to limit it to one result...

I kinda find this over the top for a such simple request, and since this request will be called a lots, I would like to optimize if as much as I can

How would you do this?


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 19 August 2014 - 09:11 AM

Why not do something like SELECT mydate, ABS(givendate-mydate), givendate-mydate ORDER BY ABS(givendate-mydate), givendate-mydate DESC;


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 19 August 2014 - 10:18 AM

oh, quite interesting!

I did try some stuff with DATEDIFF and ABS, but it always was giving me the very first entry, not the one before the closest one, but with your order by ABS, this should works


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 19 August 2014 - 12:22 PM

Compared to some of the lunacy I do in SQL, that's an easy one :) I've had to emulate a left outer join using case statements and a where clause before.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 19 August 2014 - 12:34 PM

I'm used to much more complex sql statement, but don't know why, this one, really cause me problem

 

In the end I did

order by (DATEDIFF(:myDate, dateColumn) < 0), ABS(DATEDIFF(:myDate, dateColumn)) limit 1 

So it will firstly show the one that has a negatif datediff, and then the one closest to 0


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 20 August 2014 - 05:44 AM

That will give preference to a negative difference over a close one. I think that's reverse of what you asked for, initially.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 20 August 2014 - 06:35 AM

Yes, I saw that none of thoses actually works (they only works in certains case)

 

Your method

ORDER BY ABS(givendate-mydate), givendate-mydate DESC;

WIll always give me the closest one either future or past, and only if the difference is the same, it check for the past one

 

I wish to prioritize the first one in the past (closest one in the past), and then, if there is nothing in the past, go into the future


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#8 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 20 August 2014 - 06:42 AM

gotcha


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#9 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 20 August 2014 - 07:11 AM

I think what I would need is something like this

order by dateColumn < '2014-08-20', CASE WHEN (dateColumn < '2014-08-20') THEN dateColumn DESC ELSE dateColumn ASC END

But mysql give me an error syntax, anyway I could rewrite this as a valid command?


This is a weird syntax, but I think I made it work

order by dateColumn < '2014-08-20', CASE WHEN (dateColumn < '2014-08-20') THEN dateColumn END DESC, CASE WHEN (dateColumn >= '2014-08-20') THEN dateColumn END ASC

It's working in my tests, what do you think?


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#10 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 20 August 2014 - 08:32 AM

I'm not an expert on MySQL syntax, by any means, but I'll agree it looks weird :)


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#11 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 20 August 2014 - 06:47 PM

Why not to use the Min ? I mean, like Min( abs(date1 - date2) ).

in case you have trouble with the negative or positive result priority you can always reverse the criteria by multiplying by -1 before applying the ABS.



#12 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 21 August 2014 - 04:42 AM

I think that Min( abs(date1 - date2) ) will not give me the closest negatif one, but the first negatif one

So if I had thoses dates

2014-01-01

2014-01-02

2014-01-03

 

And I search for 2014-01-04

It will give me 2014-01-01 insted of 2014-01-03


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download