CODEWITHSUNDEEP
phpmysqltimestamp
Humphrey
Humphrey

Reputation: 2817

TIMESTAMPDIFF works with day but not Minute

I have a problem with my query , where by even if I post now the post ,the TIMESTAMPDIFF Does not work with MINUTE . But if I put DAY OR MONTH it works I really can’t figure out what might be the problem. I have posts which are newer than seconds. Please any help will be appreciated 

SELECT h2s_job.job_id,
       h2s_job.job_title,
       h2s_job.job_description,
       h2s_job.job_category,
       h2s_job.wantORoffering,
       h2s_job.address,
       h2s_job.cell,
       h2s_job.email,
       h2s_job.date_registered,
       h2s_ad_info.date_registered,
       h2s_job.user_id
FROM h2s_job
INNER JOIN h2s_ad_info ON h2s_job.user_id=h2s_ad_info.user_id
WHERE TIMESTAMPDIFF(MINUTE,h2s_ad_info.date_registered ,NOW())<1

Upvotes: 1

Views: 308

Answers (3)

Musa
Musa

Reputation: 57

I discovered your problem : it starts right from the point where u insert time : Go to your database make date_registered datatype to be datetime : Then use the bellow function and use $date_registered to insert in your database: change the country and city to get the correct time because that is where u get the problem : I am sure the time u have is not the same on your system that is why . so target your country and city 

<?php
    # function to get right time 
    date_default_timezone_set('Africa/Johannesburg');
    function time_diff_conv($start, $s)
    {
        $string="";
        $t = array( //suffixes
            'd' => 86400,
            'h' => 3600,
            'm' => 60,
        );
        $s = abs($s - $start);
        foreach($t as $key => &$val) {
            $$key = floor($s/$val);
            $s -= ($$key*$val);
            $string .= ($$key==0) ? '' : $$key . "$key ";
        }
        return $string . $s. 's';
    }

date_registered = date('Y-m-d H:i:s');

# use this if u wanna show day friday like that date('l jS \of F Y \a\t h:i:s A')."\n";
    # function to get right time 
?>

Upvotes: 1

Tony Stark
Tony Stark

Reputation: 8084

Try this,

"SELECT TIMESTAMPDIFF(MINUTE, h2s_ad_info.date_registered, now()),
           h2s_job.job_id,
           h2s_job.job_title,
           h2s_job.job_description, 
           h2s_job.job_category,
           h2s_job.wantORoffering,
           h2s_job.address,h2s_job.cell,
           h2s_job.email,
           h2s_job.date_registered,
           h2s_job.user_id 
    From h2s_job
    INNER JOIN h2s_ad_info
    ON  h2s_job.user_id=h2s_ad_info.user_id
    where (TIMESTAMPDIFF(MINUTE, h2s_ad_info.date_registered, now()) < 1)";

may this help you.

Upvotes: 1

Anjoola
Anjoola

Reputation: 128

I'm assuming date_registered is a TIMESTAMP or DATETIME. If so, you can do this:

where NOW() - SECOND(h2s_ad_info.date_registered) < 1

Upvotes: 1

Related Questions