Compare dates in sql

Asked

Viewed 3,840 times

0

In my bank I have the field with datetime current_timestamp in the access table. I need to display the amount that each device accessed the site, whether computer, Ios or android, each time one of them accesses, the corresponding field gets 1, so I need to count on the day how many times the site got access from each device, problem is: how do I convert datetime into query to compare only date without time? I did that way but it worked yesterday, but the access data were yesterday, today already gave problem.

<?php
    $select= "SELECT distinct CAST(data_acesso AS DATE) FROM tbl_acesso";
    $result_select= $conn->query($select);
    while($col = mysqli_fetch_assoc($result_select)):
        $data = $col['data'];
        $data_create = date_create($data);
        $data_format = date_format($data_create, "Y-m-d");

        //selecionando site
        $selectSite = "SELECT site FROM tbl_acesso WHERE CAST(data_acesso as DATE) = '$data_format' AND site IS NOT NULL ";
        $resultadoSite = $conn->query($selectSite);
        $site = mysqli_num_rows($resultadoSite);

        //selecionando ios
        $selectIos = "SELECT ios FROM tbl_acesso WHERE CAST(data_acesso as DATE) = '$data_format' AND ios IS NOT NULL";
        $resultadoIos = $conn->query($selectIos);
        $ios = mysqli_num_rows($resultadoIos);

        //selecionando android
        $selectAndroid = "SELECT android FROM tbl_acesso WHERE CAST(data_acesso as DATE) = '$data_format' AND android IS NOT NULL";
        $resultadoAndroid = $conn->query($selectAndroid);
        $android = mysqli_num_rows($resultadoAndroid);
?>
php mysql
  • 1

    you better remove that edit and add an answer.. so you can mark it as accepted and the question is not open

  • got it, I’m gonna do it

2 answers

1


The dice must be a datetime or smalldatetime, so it keeps both values. You can do so:

convert(date,@data1) = convert(date,@data2)

So you compare by ignoring hours as both are converted from DATETIME to DATE (no time).

I hope I’ve helped.

  • In the case of your database, I think it has to be cast(data_access as dates) = cast('$data_formatted',date)

0

I decided to do it here guys

SELECT DATE_FORMAT(data_acesso, '%Y-%m-%d') AS data_acesso FROM tbl_acesso;

In sgbd it worked, but in php it was not pq needed an alias.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.