Cheam Huoy San Cheam Huoy San - 1 year ago 57
SQL Question

SQL query to retrieve latest 2 week records

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

Answer Source

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.

    TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((CreatedDate-18000)
    /(60*60*24)) GOOD_DATE
    GOOD_DATE >=
    (SELECT MAX( TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+
    ((CreatedDate-18000) /(60*60*24))) -14