SQL to compare two unix timestamps?

Question

Hi,

I have a table with a field that contains unix timestamps for each record. I would like to return each row where the year and month are equal to the current year and month.

Is there a simple way to do this?

Thanks

Answer 1

This will convert your unix timestamp (which I'm assuming is stored in a numeric-typed column of some sort) to a datetime:

SELECT dateadd(s, MyTimeStampCol, '19700101')

Given that, you should be able to do something like this:

SELECT  *  --(Your columns)
FROM    MyTable
WHERE   YEAR(dateadd(s, MyTimeStampCol, '19700101')) = year(getdate())
AND  month(dateadd(s, MyTimeStampCol, '19700101')) = month(getdate())

BTW - I have no experience with Unix timestamps, so I'm using this as a reference:

http://www.unixtimestamp.com/index.php

edit: if you're not on SQL Server, you'll need to use CURRENT_TIMESTAMP in lieu of getdate

edit again: come to think of it, if you're not using SQL Server, I'm not sure if dateadd will work. Can't tell I work with SQL Server almost exclusively, can you?

Answer 2

man ctime

Compute upper and lower limits on the Unix time value for the month and year you are interested in, then do a simple SQL compare.

Answer 3

SQL Server 2005+:

WITH dates AS (
    SELECT t.timestampCol,
            YEAR(DATEADD(s, t.timestampCol, '19700101')) 'year',
            MONTH(DATEADD(s, t.timestampCol, '19700101')) 'month'
       FROM TABLE t)
SELECT t.*
 FROM TABLE t
 JOIN dates d ON d.timestampCol = t.timestampCol
WHERE d.year = YEAR(GETDATE()) 
  AND d.month = MONTH(GETDATE())

Oracle 9i+:

WITH dates AS (
     SELECT t.timestampCol,
            EXTRACT(YEAR FROM t.timestampCol + (1/(24*60*60) * 19700101) 'year',
            EXTRACT(MONTH FROM t.timestampCol + (1/(24*60*60) * 19700101) 'month'
       FROM TABLE t)
SELECT t.*
 FROM TABLE t
 JOIN dates d ON d.timestampCol = t.timestampCol
WHERE d.year = EXTRACT(YEAR FROM SYSDATE) 
  AND d.month = EXTRACT(MONTH FROM SYSDATE)