CODEWITHSUNDEEP
phpmysqlsqldateunix-timestamp
PHPLover
PHPLover

Reputation: 13005

How to use UNIX Timestamp value for today's date in PHP/MYSQL query?

I want to fetch all the records whose transaction_date falls today e.g. (12th February 2014). So to achieve this I tried following SQL query:

SELECT transaction_status, count( * ) AS StatusCount
FROM OCN.user_transaction
WHERE transaction_date = date('Ymd')
GROUP BY transaction_status

But it's not working for me. Can anyone help me in this regard please? Thanks in advance.

Upvotes: 2

Views: 1571

Answers (6)

Ethic Or Logics
Ethic Or Logics

Reputation: 109

The current date method returns today's date...So i believe the post that was submitted by hd1 should work.

SELECT transaction_status, COUNT(*) AS StatusCount FROM OCN.user_transaction WHERE transaction_date = CURDATE() GROUP BY transaction_status

Upvotes: 1

Neels
Neels

Reputation: 2543

Try this:

SELECT 
transaction_status,
COUNT(*) AS StatusCount 
FROM
OCN.user_transaction 
WHERE transaction_date =date_format(CURRENT_TIMESTAMP, "%d-%m-%y")
GROUP BY transaction_status ;

You can modify the format as you want.

Upvotes: 1

Jesper
Jesper

Reputation: 599

You are comparing 2 different date-time formats, so parse the 'transaction_date' to the same date-time format as the input date. Use DATE_FORMAT(date,format) for this problem.

example implementation:

    SELECT transaction_status, 
         count( * ) AS StatusCount 
    FROM OCN.user_transaction 
   WHERE DATE_FORMAT(transaction_date, "Ymd") = date('Ymd') 
GROUP BY transaction_status

Good luck!

Upvotes: 1

Arthur Grishin
Arthur Grishin

Reputation: 358

If data type is BigInt(12) then this should work for you:

SELECT
    transaction_status, 
    count( * ) AS StatusCount 
FROM 
    OCN.user_transaction 
WHERE 
    DATE_FORMAT(FROM_UNIXTIME(transaction_date), "%Y-%m-%d") = DATE(NOW()) 
GROUP BY 
    transaction_status

But using BigInt to store timestamps is not the best way to store date in MySQL. You can use DATE, DATETIME or TIMESTAMP, depending on your needs.

Upvotes: 3

hd1
hd1

Reputation: 34677

You would do worse than to use the CURDATE function in MySQL:

SELECT 
  transaction_status,
  COUNT(*) AS StatusCount 
FROM
  OCN.user_transaction 
WHERE transaction_date = CURDATE()
GROUP BY transaction_status

If you've further questions, do leave a comment.

Upvotes: 1

qwert_ukg
qwert_ukg

Reputation: 490

if transaction_date is timestamp type, and query create by php, try this:

SELECT 
  transaction_status,
  COUNT(*) AS StatusCount 
FROM
  OCN.user_transaction 
WHERE transaction_date = <?php time() ?>
GROUP BY transaction_status

else try mysql now() function

SELECT 
  transaction_status,
  COUNT(*) AS StatusCount 
FROM
  OCN.user_transaction 
WHERE transaction_date = now()
GROUP BY transaction_status

Upvotes: -2

Related Questions