sql syntax help needed

pelegk2

i have a table with a col that hold the date+time
i want for a certain month X to get for each tday only the most last updates recored!
is it possible?
then how?
thanks in advance
peleg

Danicek

Of course it is possible. You should use build-in functions. And because these functions differ in every db server, you should add what server you are using (I guess mySQL).

pelegk2

server
and the query is for all of the days of a certain month!
how do i do that?
thanks in advance
peleg

Danicek

Select Max(date_collumn_name)
   From table_name
   Where month(date_collumn_name) = $month
   Group By dayofmonth(date_collumn_name)

You wrote all of the days of a certain month, so I suppose you know the month. Add it in place of $month variable.
I hope it is possible to use date functions in group gy clause in mySQL, otherwise you will need other way to solve this.

pelegk2

my dte column is in long int format (unix_timestamp)
what do i do in this case?>

ps: u corect i know the month bu i ned the max for each day
thanks alot
peleg

Danicek

Then you need to transform it in where clause to datetime (you do not need it in max function), so:

Select Max(date_collumn_name)
   From table_name
   Where month(from_unixtime(date_collumn_name)) = $month
   Group By dayofmonth(from_unixtime(date_collumn_name))

pelegk2

u are the man!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

1 more question :
is there something like from_unixtime in php?

Danicek

I do not know about such function in PHP (but I am not right one for PHP functions).
However it is not so difficult to do it manualy because UNIX_TIMESTAMP are seconds since '1970-01-01 00:00:00' GMT.

pelegk2

Max(date_collumn_name) as tmpDate