SQL query to retrieve latest 2 week records

Question

I have a database with CreatedDate is store in Unix epoch time and some other info. I want a query to able to retrieve latest 2 week record base on the last record. Below is part of the example

ID   User       Ranking     CreatedDate
-------------------------------------------------------
1   B.Sisko       1        1461136714  
2   B.Sisko       2        1461123378
3   B.Sisko       3        1461123378
4   B.Sisko       3        1461600137
5   K.Janeway     4        1461602181
6   K.Janeway     4        1461603096
7   J.Picard      4        1461603096

The last record CreatedDate is 25 Apr 2016, so I want the record from 12 Apr to 25 Apr.

I not sure how to compare to get latest data? any suggestion

So you can convert the time like here http://stackoverflow.com/questions/23994889/converting-epoch-number-to-human-readable-date-in-mysql, and MAX(id) and MAX(CreatedDate) should be the latest one. — dmitryro, Jun 29 '16 at 01:23

Gordon Linoff · Answer 1 · 2016-06-29T02:08:31.273

The simplest method is probably to just subtract two weeks from today's date/time:

where CreatedDate >= UNIX_TIMESTAMP() - 7*24*60*60

Another approach is to convert the value to a date/time:

where from_unixtime(CreatedDate) >= date_sub(now(), interval 2 week)

The advantage of this approach is that it is easier to align to days. So, if you want two weeks starting at midnight:

where from_unixtime(CreatedDate) >= date_sub(curdate(), interval 2 week)

The disadvantage is that the function on the column prevents the use of indices on that column.

EDIT:

This is definitely not how your question was phrased. But in that case, you should use:

select t.*
from t cross join
     (select from_unixtime(max(CreatedDate)) as maxcd from t) m
where from_unixtime(CreatedDate) >= date_sub(maxcd, interval 2 week);

Thanks for your suggestion, however, the record I want to get is the latest 2 week record base on the last entry, not from today. @Gordon Linoff — Cheam Huoy San, Jun 29 '16 at 01:27

score 0 · Accepted Answer · answered Jun 29 '16 at 01:53

0

It may seem odd, but you need to execute two queries one to find the Maximum Date and knock off 14 days -- and then use that as a condition to requery the table. I used ID_NUM since ID is a reserved word in Oracle and likely other RDBMS as well.

    SELECT ID_NUM, USER, RANKING,
    TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((CreatedDate-18000)
    /(60*60*24)) GOOD_DATE
    FROM MY_TABLE
    WHERE
    GOOD_DATE >=
    (SELECT MAX( TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+
    ((CreatedDate-18000) /(60*60*24))) -14
    FROM MY_TABLE)

answered Jun 29 '16 at 01:53

Charlie K

114
5

1

`to_date()` is not a MySQL function. I have no idea why this answer would be accepted. – Gordon Linoff Jun 29 '16 at 02:09
Gordon: Of course you're a genius I missed seeing the mysql tag, and looked to find an answer for the OP in the world I work in which is first Oracle and 2nd MS Access Of course the key to her question was using the subquery -- so perhaps that was what made her happy. The date handling perhaps wasn't the most important piece – Charlie K Jun 29 '16 at 04:44

SQL query to retrieve latest 2 week records

2 Answers2