1

I'm trying to get the rows from a table where lastLoginTimestamp is in last 7 days and I'm doing as follow:

SELECT * FROM representative WHERE DATE_SUB(lastLoginTimestamp, INTERVAL 7 DAY)

lastLoginTimestamp is stored as a VARCHAR so this is where the problem is I though. How do get that rows in this case? Any help?

This are some of the values on that column to play with:

1427162434
1422557283
1395854997

Update

Based on the first answer I've changed my query to this:

SELECT
    *, from_unixtime(lastLoginTimestamp) AS lastLogin
FROM
    representative
WHERE
    DATE_SUB(
        from_unixtime(lastLoginTimestamp),
        INTERVAL 7 DAY
    )

But I'm getting the wrong values, for example I'm getting a row from this lastLogin = 2015-03-23 21:30:34 which is wrong

  • Why, oh why, do you store timestamps as strings? – ypercubeᵀᴹ Apr 7 '15 at 16:25
  • @ypercube wasn't me I'm just getting the works from others and yes I know this is insane and is wrong but I can't change at this moment, will do later in a new development – ReynierPM Apr 7 '15 at 16:26
  • There are functions to convert to and from timestamp type. – ypercubeᵀᴹ Apr 7 '15 at 16:27
  • [specify the format and convert it before the rest][1] [1]: stackoverflow.com/questions/13709744/… can you tell me what '1422376080' means as date (wheat do you expect to see) so I can give you the right answer – ninjabber Apr 7 '15 at 16:27
  • @ypercube You mean execute a function on that column? – ReynierPM Apr 7 '15 at 16:28
3

The values seem to be Unix timestamps, stored as strings. You can use the special datetime functions that MySQL has for conversions:

SELECT * 
FROM representative 
WHERE DATE_ADD(FROM_UNIXTIME(lastLoginTimestamp), INTERVAL 7 DAY) >= NOW() ;

but it won't be very good for efficiency. Much more efficient, if there is an index on the column, to do it the other way around:

(we assume that the values in the columns are CHAR(10). If some values have smaller length, you'll get erroneous results.)

SELECT * 
FROM representative 
WHERE lastLoginTimestamp 
      >= CAST(UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY) AS CHAR(10));

The above condition if you want all rows, starting from exactly 7 days ago, same time as now. If you want rows starting from 6 days ago at midnight, so previous 6 days + today (whatever that is, a few minutes or 23 hours, 59 mins):

SELECT * 
FROM representative 
WHERE lastLoginTimestamp 
      >= CAST(UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 6 DAY) AS CHAR(10));
  • +1 but I would recommend that the OP reads the links in the comments – ninjabber Apr 7 '15 at 16:47
  • @ninjabber I'm on it, thanks both, ypercube awesome it works prefectly – ReynierPM Apr 7 '15 at 16:48
  • @ninjabber You can edit your answer and include any links you think are useful. – ypercubeᵀᴹ Apr 7 '15 at 16:54
  • I like giving mysterious answers – ninjabber Apr 7 '15 at 16:57
1
select from_unixtime("1427162434");

the rest you will figure out

but consider formatting the historical data and just change the type and test. Can't believe that the dates are pushed to the DB as chars.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.