tags:

views:

40

answers:

3

Hi,

Is it possible to do sql query on what day is today and get the row of today for date column?

So let say today is july 25th, i have database table sales, column name date, date = sales transaction date in timestamp.

i need all row that the sales date is same with current date and also is it possible set value as gmt+5?

+1  A: 

This will get you all the rows for today's date:

SELECT * FROM sales 
WHERE DATE(NOW()) = DATE(DATE_ADD(sales_transaction, INTERVAL 5 HOUR)) 
ORDER BY sales_transaction

As for GMT +5, Do you mean all rows with a sales date +5 hours or today +5 hours?

EDIT: Updated to add 5 hours to sales date. For a column called date, I would use the back-ticks to indicate it's a column name. e.g. SELECT `date`` FROM sales

I can't figure out how to work the back-ticks on the date field. But you should get the idea. Wrap your column names with `

Brendan Bullen
all rows with sales date +5, if the column name is date, will it cause any problem with mysql function DATE?
BobDylan
Hi again, its not working, so to debug when i tried SELECT DATE(DATE_ADD(timestamp_of_sales, INTERVAL 5 HOUR)) FROM sales, it return as NULL
BobDylan
Hmmmm, seems to work for me. Is the timestamp in the format 'yyyy-mm'dd hh:mm:ss'? Or is it a UNIX Timestamp?
Brendan Bullen
A: 

Give some time and Check out Date Time Function of mySQL

  SELECT * FROM tablename WHERE salesdate = '1998-1-1';

If your date is stored in GMT timezone

Select *
FROM tablename
WHERE DATE_ADD(NOW(), INTERVAL 5 HOUR) = salesdate 
Manjoor
A: 
SELECT * FROM `sales` WHERE DAYOFMONTH(FROM_UNIXTIME(date)) = DAYOFMONTH(DATE(now()))

seems working.

Thank you for all of your replies.

BobDylan
I think this will give you results for any sales that match the day of the month regardless of which month they occurred in. So, for today (4th of August), you will get sales for the 4th of August, 4th of July, 4th of June etc.
Brendan Bullen