[SOLVED] Selecting jan, feb from unix timestamp

[mapleleaf]

I am sure this has been asked a lot but my search isn't finding it.

 

My database dates are stored as 1238468578 for example.

 

Is there a MySQL function that can search for feb or march 2009 for example?

Basically I need to able to organise entries into each month of the year

Tx

 

[Festy]

No, there ain't any mysql function which will fetch months from a unix timestamp for you. You need to use some mathematical calculation on a fetched date to get the month.

[mapleleaf]

So i ought to store a mysql timestamp too it seems.

Sounds like an easier option.

[PFMaBiSmAd]

A Unix Timestamp requires a conversion to be usable for most things, so using a mysql DATE, DATETIME, or TIMESTAMP usually results in the fastest operating queries.

 

To just select a range of Unix Timestamps, you can form the starting and ending timestamp for the range you want. However, to "organize entries into each month of the year" it is much easier if you have a DATE data type.

 

You can use the mysql FROM_UNIXTIME() function to get a standard DATE format that you can then use to separate and organize data by months of the year, but this is not the optimum solution - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

[mapleleaf]

ok so if i store the dates as DATE in mysql then I can do the calculations for each month using MySQL functions?

 

So to select the average value for each month  starting from the first time entered would be something like:

$i = date('m',time());
SELECT avg(quantity) as q WHERE user_id = '$uid' AND date_added = CURRENT_DATE - $i MONTH

 

and somehow get that into a while loop up to the present.

 

I can't seem to get my head round Mysql time. Php seems easier.

 

[chrispos]

The best way I have found is to put a month and year field in the table and use php

$d = $_POST['d'];
$m = $_POST['m'];
$y = $_POST['y'];

Insert them into the table and you can call then

SELECT * FROM sometabel WHERE month = $m day = $d AND year = $y

use more php to get them as a date

$date = date ("M-d-Y", mktime(0, 0, 0, $m, $d, $y));

I hope this helps

 

[chrispos]

The best way I have found is to put a month and year field in the table and use php

$d = $_POST['d'];
$m = $_POST['m'];
$y = $_POST['y'];

Insert them into the table and you can call then

SELECT * FROM sometabel WHERE month = $m AND day = $d AND year = $y

use more php to get them as a date

$date = date ("M-d-Y", mktime(0, 0, 0, $m, $d, $y));

I hope this helps

[mapleleaf]

I have found a way.

A bit more long winded than yours as now i select

MONTH(timestamp) as month,  YEAR(timestamp) as year

 

and then increase them till i get to time();

 

Odd mix of php and MySQL functions that I wouldn't recommend hence not posting it.

It works well enough.