0

I need a way to query a collection of data. I have a list of recent activity dates each stored in 1 row per user. Each row has a field of loginDates which consists of a comma separated list of timestamps.

What i need to do is run reports on this date to find people active since XXXXXX timestamp. The problem is the fact it's comma separated means i can't query it uses methods i know.

Here is an example row

id  userID  accessDates
2   6   1399494405,1399494465,1399494525,1399494585,1399494623

What i want to achieve in plain text

SELECT all_fields FROM accessTable WHERE accessDate > YESTERDAY

ALSO These dates may however span over several hundreds of days with hundreds of timestamps in the field.

slaterjohn
  • 249
  • 2
  • 9
  • 10
    What a great reason to learn about proper database design, where you don't store date/time values as strings, and you don't put lists of things in comma delimited columns. That is why relational databases have tables. Use them. And, learn about junction tables (also called association tables). – Gordon Linoff May 07 '14 at 20:46
  • 2
    Rather than trying to cobble together a solution to this, you should fix the data model. If those are dates, they should be saved AS dates. And each record should have their own row. You'll find it much easier to actually work with the data then. – durbnpoisn May 07 '14 at 20:49
  • 1
    Changing the structure isn't an option. I'm trying to design reports based on the data that exists. Hence my question. – slaterjohn May 08 '14 at 09:12

1 Answers1

0

Assuming the TimeStamp values are in order as your data sample shows, if any of the TimeStamp values in the string are greater than a given date, then the latest one would be greater than that value as well. So you only need the latest TimeStamp value to meet your requirement:

SET @Yesterday =
  UNIX_TIMESTAMP(DATE_ADD(DATE(CURRENT_TIMESTAMP()),INTERVAL -1 DAY));

SELECT *
FROM accessTable
WHERE  CAST(RIGHT(accessDates,10) AS UNSIGNED) > @Yesterday;

If you want to query each of those TimeStamps individually, the best solution is to put them into a single table column with a userid:

userID    accessDate
------    ----------
6         1399494405
6         1399494465
6         1399494525
6         1399494585
6         1399494623
Ron Smith
  • 3,241
  • 1
  • 13
  • 16
  • This may make more sense for me. I've got the ability to add a table. So i could run a query before the report to separate all dates from comma separated fields into rows as you suggested. Can separating each row's comma separated values be done directly within a query? – slaterjohn May 08 '14 at 09:17
  • In MySql, you would need to create a procedure to loop through the accessTable records and parse the string: http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql – Ron Smith May 08 '14 at 16:45
  • Cheers! :) If I have helped you, please consider marking mine as the best answer or letting me know what additional info I can add. – Ron Smith May 09 '14 at 19:41