MySQL statement to fetch rows between unix timestamps

Question

Hello I have a problem with fetching required rows from MySQL table correctly within date interval.

My table has had such structure originally (yes with unixtimestamps not mysql dates):

    id    name    departures                   depmin         depmax 
-------------------------------------------------------------------------
    1      a      1327608000                 1327608000     1327608000

    2      b   1321646400,1322856000,        1321646400     1337976000
               1325880000,1327694400,
               1329508800,1330718400,
               1331928000,1332532800,
               1333137600,1333742400,
               1334347200,1334952000,
               1336161600,1336766400,
               1337371200,1337976000

    3      c   1315166400,1316894400,        1315166400    1327780800
               1317758400,1318968000,
               1319918400,1320004800,
               1320091200,1320177600,
               1321387200,1324152000,
               1325448000,1325534400,
               1325620800,1327780800
------------------------------------------------------------------------

My task is to get all 3 rows with departure between 1327536001 and 1327881601 but if I query like so:

SELECT * FROM exampletable WHERE depmin >= 1327536001 AND depmax <= 1327881601

I only get the first row (with id 1 and name a). So I'm totally confused how to get all three rows between this example interval?

Please advise how to construct my where query or how to reformat the table.

Well, the other two rows are not exactly in this interval - at best they're overlapping. — Sergii Kudriavtsev
– Sergii Kudriavtsev, Commented Jan 19, 2012 at 19:22

Justin Lucas · Accepted Answer · 2012-01-19 19:21:08Z

2

You would get all 3 rows returned if the depmin values for the b and c were actually greater than 1327536001. Looks like a data issue. The SQL query looks good to me.

answered Jan 19, 2012 at 19:21

Justin Lucas

2,3111 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Krelin - hacker · Accepted Answer · 2012-01-19 19:21:24Z

2

You get perfectly valid result, the first row is the only one with depmin greater than or equal 1327536001 and depmax less than or equal 1327881601. If you only want to limit depmin or only depmax, ask for it.

answered Jan 19, 2012 at 19:21

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Comments

Sergii Kudriavtsev · Accepted Answer · 2012-01-19 19:46:39Z

If I understand your question correctly, you want to get all the id's where any timestamps from column departures fall within specific interval.

Unfortunally, there is no simple way to achieve that. You'll have to either write an extensive stored procedure/function (involving parsing string from departures column, putting results in array variable or temporary table, comparing them to your borders etc.) or you'll have to redesign your database heavily.

Personally, I'd suggest to move data from departures column into separate table, one departure time per row, like this:

   id            departure
------------------------------
    1        1327608000
    2        1321646400
    2        1322856000
...

Then you will be able to satisfy your original requirements with query like

SELECT * FROM exampletable WHERE id IN (SELECT id FROM departures WHERE departure >= 1327536001 AND departure <= 1327881601)

Marc B · Accepted Answer · 2012-01-19 19:26:33Z

1

Look at your 4 table records like this:

1. depmin: 1327608000
   query:  1327536001      depmin >= query -> TRUE

   depmax: 1327608000
   query : 1327881601      depmax <= query -> TRUE

TRUE and TRUE -> TRUE

2. depmin: 1321646400
   query : 1327536001      depmin >= query -> FALSE

   depmax: 1337976000
   query : 1327881601      depmax <= query -> FALSE

FALSE and FALSE -> FALSe

If, if I'm reading your question right, you're just trying to find any rows where the query timestamp ranges overlap the database table timestamps, then your logic should actually be:

WHERE depmax >= 1327536001 AND depmin <= 1327881601
      ^^^^^^----- reversed ----^^^^^^

That way you'll get ANY record where its min/max values fall within the bounds of your query's ranges.

answered Jan 19, 2012 at 19:26

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

moogeek Over a year ago

Yes, that's right for my question. Thank you. But I can't obviously show the right example based on my production table data where I still have the problem.

Dmitry Ovsyanko · Accepted Answer · 2012-01-19 19:24:58Z

0

Maybe you have to swap depmin and depmax in your query condition.

That will mean "intervals having intersection with [1327536001 ... 1327881601]"

answered Jan 19, 2012 at 19:24

Dmitry Ovsyanko

1,41611 silver badges6 bronze badges

Collectives™ on Stack Overflow

MySQL statement to fetch rows between unix timestamps

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related