unixtimestamp in sql

[Sandro87]

hello,
I decided to switch completely to unixtimestamp values in the database instead of using timestamp which gives me problems with timezones and I prefer working with unixtime most of all.

The only negative side is that with timestamp values in the db I could easily (for instance) select a month with month() directly in the query or work with components anyway, now with unixtime being just numbers I can't. I discovered from_unixtime() which should convert unixtime to timestamp in 0000-00-00 00:00:00. How can I use this function in 1 query?

select id_news,title,extract(year_month from date) as new_month_year from news order by new_month_year desc,id_news desc

considering date is no longer 0000-00-00 00:00:00 but 0000000000 I tried:

select id_news,title,extract(year_month from (from_unixtime(date)) as new_month_year from news order by new_month_year desc,id_news desc

but doesn't work.

any help?

[rocknbil]

I'm not sure what you're saying . . .

select unix_timestamp(now()),now();

--> 1259384401 ¦ 2009-11-27 21:00:01

select extract(month from (from_unixtime('1259384401'))), extract(month from ('2009-11-27 21:00:01'));

--> 11 ¦ 11

select extract(second from (from_unixtime('1259384401'))), extract(second from ('1009-11-27 21:00:01'));

--> 1 ¦ 1

[Sandro87]

Yep that's what I wanted , I just forgot a ")"