phpmyadmin Question posted in
The official documentation can be found here.

Different Mysql date Results On website and in PHPmyadmin

I wants to select rows which are less than with today date.

In database my date column is saving data in unix time stamp.

I am running on following query with php and mysql. (this query should show 1 row as i have 1 result in the database, but it is showing two rows.)

SELECT * from products where seller_id ='1' and FROM_UNIXTIME(marketing_end_date,'%Y-%m-%d') < CURRENT_DATE();

But when i run above query in phpmyadmin it retrun one row( my desired result).

Here is my code which is reading data from mysql.

function get_sellerenddateproducts($admin,$seller_id,$limit_per_page,$start_index){
    $this->db->select('*');
    $this->db->where(array('seller_id' => $seller_id));
    $this->db->where("FROM_UNIXTIME(marketing_end_date,'%Y-%m-%d') <","CURRENT_DATE()");
    $this->db->limit($limit_per_page, $start_index);
    $this->db->order_by('id','desc');
    $query = $this->db->from($this->_table_name)->get();
    $arr = $query->result_array();
    //echo $this->db->last_query(); die();
    return $arr;
}

Question

Why same query show different results ? How to get correct results?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I resolve my issue like below.

    I create a variable like this.

    $todaydate = date('Y-m-d');

    and change my query from

    $this->db->where("FROM_UNIXTIME(marketing_end_date,'%Y-%m-%d') <","CURRENT_DATE()");

    to this

    $this->db->where("FROM_UNIXTIME(marketing_end_date,'%Y-%m-%d') <",$todaydate);

    then it gives me correct result.


  2. 0

    MySQL actually stores datetime data type fields internally as UTC.

    However, PhpMyAdmin shows you the dates using the server default time.

    Use this line to detect TimeZone of your MySQL Server:

    SELECT @@system_time_zone;

    For example, try adding this line before PhpMyAdmin SQL statement:

    Set time_zone = '+00:00';

    This sets timezone to GMT/UTC, so that any further operations will use GMT.

    Let me know if they return same result now 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search