1

Trying to utilize MySQL Date and Time functions. Currently I have a table that shows a mailman's routes for the past 5 days. These values are stored as a time stamp in my database. I want all of the Street Addresses this mailman has visited in the past 3 days.

So far, I can do this following query:

 "SELECT RouteEndTime
  FROM Mailman GROUP BY RouteEndTime
  HAVING DATEDIFF(MAX(RouteEndTime), RouteEndTime) <= 3;"

but I only get back all of the RouteEnd values for all 15 rows

I'm looking at this list of resources:

https://www.w3schools.com/sql/sql_ref_mysql.asp

Under MySQL Date Functions, but there is no function that does what I need it to do.

Is there a function that I can use to get all the street addresses he visited in the most recent 3 days (starting from the most recent date in my db) The answer I'm looking for is basically all the street address names from January 5th, 4th and 3rd.

MailManID  | Street Address |  RouteStartTime        |  RouteEndTime

54         |  'Apple St'    |  '2018-01-05 09:00:00' | '2018-01-05 09:05:00'
54         |  'Bat St'      |  '2018-01-05 09:30:00' | '2018-01-05 09:35:00'
54         |  'Cat St'      |  '2018-01-05 09:45:00' | '2018-01-05 09:50:00'

54         |  'Kite St'     |  '2018-01-04 09:00:00' | '2018-01-04 09:05:00'
54         |  'Lemon St'    |  '2018-01-04 09:30:00' | '2018-01-04 09:35:00'
54         |  'Muppet St'   |  '2018-01-04 09:45:00' | '2018-01-04 09:50:00'

54         |  'Luke St'     |  '2018-01-03 09:00:00' | '2018-01-03 09:05:00'
54         |  'Ben St'      |  '2018-01-03 09:30:00' | '2018-01-03 09:35:00'
54         |  'Cow St'      |  '2018-01-03 09:45:00' | '2018-01-03 09:50:00'

54         |  'Hello St'    |  '2018-01-02 09:00:00' | '2018-01-02 09:05:00'
54         |  'Igloo St'    |  '2018-01-02 09:30:00' | '2018-01-02 09:35:00'
54         |  'Jump St'     |  '2018-01-02 09:45:00' | '2018-01-02 09:50:00'

54         |  'Yellow St'   |  '2018-01-01 09:00:00' | '2018-01-01 09:05:00'
54         |  'Blue St'     |  '2018-01-01 09:30:00' | '2018-01-01 09:35:00'
54         |  'Red St'      |  '2018-01-01 09:45:00' | '2018-01-01 09:50:00'

I want to return something like:

'Apple St' '2018-01-05 09:00:00' '2018-01-05 09:05:00'
'Bat St' '2018-01-05 09:30:00' '2018-01-05 09:35:00'
'Cat St''2018-01-05 09:45:00' '2018-01-05 09:50:00'
'Kite St''2018-01-04 09:00:00' '2018-01-04 09:05:00'
'Lemon St''2018-01-04 09:30:00' '2018-01-04 09:35:00'
'Muppet St''2018-01-04 09:45:00' '2018-01-04 09:50:00'
'Luke St''2018-01-03 09:00:00' '2018-01-03 09:05:00'
'Ben St''2018-01-03 09:30:00' '2018-01-03 09:35:00'
'Cow St''2018-01-03 09:45:00' '2018-01-03 09:50:00'
baconLikeTheKevin725
  • 225
  • 1
  • 4
  • 11
  • 1
    See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jan 09 '18 at 00:56
  • Incidentally, a unix timestamp is simply the number of seconds since Jan 1st 1970. What you've provided above is just a timestamp – Strawberry Jan 09 '18 at 01:03
  • Thanks for the tip, @Strawberry. Regarding the link you posted, is the table I've provided not good enough or missing some important information? – baconLikeTheKevin725 Jan 09 '18 at 01:13
  • Both , I'd say. But the accepted answer at the link provided is very thorough. – Strawberry Jan 09 '18 at 07:24

1 Answers1

3

A better source of information about MySQL is straight from their own documentation. In that list you will see DATEDIFF() which gives the difference in days between two dates.

... WHERE DATEDIFF(NOW(), RouteStartTime) <= 3

or something like that will get you what you need.

If you want exactly three days to the second, it's easier just to use the timestamps:

... WHERE (UNIX_TIMESTAMP() - 60*60*24*3) <= UNIX_TIMESTAMP(RouteStartTime)

(60*60*24*3 is the number of seconds in three days)

To measure your three days from the last timestamp in your database, you need to use the MAX() function. Cribbing from this answer, we see that WHERE won't allow MAX() (except as a subquery, but HAVING will:

... HAVING DATEDIFF(MAX(RouteStartTime), RoutStartTime) <= 3

HAVING is supposed to be for aggregate functions, but works for MAX() without a GROUP BY clause.

Brentspine
  • 274
  • 1
  • 15
Jerry
  • 3,391
  • 1
  • 19
  • 28
  • Thanks @Jerry - I understand the DATEDIFF function better now however, what if I wanted the the last 3 days from the last day in the data set? So it would be the last 3 days starting from the most recent date? – baconLikeTheKevin725 Jan 09 '18 at 01:54
  • 1
    That is trickier. Rather than NOW(), you need to use the aggregate function MAX() on the column, with requires a subquery. Fortunately an easy one. I'll update the answer. – Jerry Jan 09 '18 at 01:59
  • So, @Jerry in omitting the GROUP BY in the above statement, doesn't it just default to behaving like the WHERE clause? – baconLikeTheKevin725 Jan 09 '18 at 02:26
  • It does (pretty much), but still allows `MAX()`, where `WHERE` does not. Note that in that answer I linked to, other answers recommend a join on the same table instead. – Jerry Jan 09 '18 at 19:08