Dear All,
I am a newbie to MySQL.
My table stores time in unix_timestamp type of column,
I can list it in date format using
from_unixtime(createdtime/1000,'%d-%m-%Y %H:%i:%S')

But how to compare it with dates?

createdtime is a field storing unix_timestamp then how can i retrieve
records having values greater that '01-Mar-08'? What are the related
functions?

Thanks in advance.

Regards,
Sameer

Sameer wrote:
> Dear All,
> I am a newbie to MySQL.
> My table stores time in unix_timestamp type of column,
> I can list it in date format using
> from_unixtime(createdtime/1000,'%d-%m-%Y %H:%i:%S')
>
> But how to compare it with dates?
>
> createdtime is a field storing unix_timestamp then how can i retrieve
> records having values greater that '01-Mar-08'? What are the related
> functions?
>
> Thanks in advance.
>
> Regards,
> Sameer
>
>


http://dev.mysql.com/doc/refman/5.0/...functions.html

This comes to mind - not tested.
datediff(from_unixtime(createdtime/1000,'%d-%m-%Y %H:%i:%S'),'1997-11-30
23:59:59')

from the docs:
"If you want to subtract UNIX_TIMESTAMP() columns, you might want to
cast the result to signed integers. See Section 11.9, “Cast Functions
and Operators”."

I tend to stay away from UNIX_TIME and use DATE field and NOW() for
those "audit-trail" type functions. Keep in mind that UNIX_TIME has
problems with dates prior to 1970 and certain timezones

see the docs
"Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert
between TIMESTAMP values and Unix timestamp values, the conversion is
lousy because the mapping is not one-to-one in both directions. For
example, due to conventions for local time zone changes, it is possible
for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix
timestamp value. FROM_UNIXTIME() will map that value back to only one of
the original TIMESTAMP values. Here is an example, using TIMESTAMP
values in the CET time zone"