CODEWITHSUNDEEP
phpmysqldateunix-timestamp
PHPLover
PHPLover

Reputation: 12957

How to group records by date when date is in UNIX Timestamp format?

I'm getting two dates from PHP form in the dd-mm-yyyy format. (Say 01/06/2013 and 30/06/2013) Now I'm using following code to display the datewise result among the date range as given above, but it's not working for me as the dates stored in DB are in UNIX Timestamp format(transaction_date bigint(12)). How should I display the datewise results then? Can anyone help me in resolving this issue?

if($form_data['from_date']!='' && $form_data['to_date']!='') {
        $from_time  = explode("/", $form_data['from_date']);
        $to_time    = explode("/", $form_data['to_date']);

        $start_date = mktime( 0,0,0,$from_time[1],$from_time[0],$from_time[2] ) ;
        $end_date   = mktime( 23,59,59,$to_time[1],$to_time[0],$to_time[2] ) ;

        $sql  =" SELECT COUNT(*) `total count`, SUM(transaction_status = 'success') `success`, ";
        $sql .=" SUM(transaction_status = 'inprocess') `inprocess`, SUM(transaction_status = 'fail') `fail`, ";
        $sql .=" SUM(transaction_status = 'cancelled') `cancelled` FROM user_transaction ";
        $sql .=" WHERE  transaction_date >= '".$start_date."' AND transaction_date <=  '".$end_date."' GROUP BY transaction_date ";

        $this->mDb->Query( $sql);

        $queryResult = $this->mDb->FetchArray();
    }

Thanks in advance.

Upvotes: 1

Views: 398

Answers (2)

Code Lღver
Code Lღver

Reputation: 15603

Make change in your last line of query and replace with below code:

$sql .=" WHERE  DATE_FORMAT(transaction_date,'%d-%m-%Y') >= '".$start_date."' AND DATE_FORMAT(transaction_date,'%d-%m-%Y') <=  '".$end_date."' GROUP BY FROM_UNIXTIME(transaction_date)";

And this link will be more helpful to you MySQL convert datetime to Unix timestamp.

Upvotes: 0

mirkobrankovic
mirkobrankovic

Reputation: 2347

Use FROM_UNIXTIME(transaction_date) to get it as date type. Info

Upvotes: 2

Related Questions