Page 1 of 1

Query related

Posted: Thu Sep 13, 2007 11:32 pm
by sandy1028
Hi,

How to find the values of exactly one day old data from curtime()

Suppose time() is 12:40:56 to find data between time() and previous day 12:40:56.

Posted: Thu Sep 13, 2007 11:46 pm
by feyd
Subtract a day from that time. It's generally held that there are 24 hours in a day, 60 minutes in a hour and 60 seconds in a minute.

Posted: Fri Sep 14, 2007 12:18 am
by sandy1028
feyd wrote:Subtract a day from that time. It's generally held that there are 24 hours in a day, 60 minutes in a hour and 60 seconds in a minute.
I used the query

select * from `table_name` where `fieldname` < now() and `fieldname` > now()-interval 1 day


But I am not getting exactly 24hours data.

I am getting the data from cur timestamp till previous day data till 2007-09-13 00:00:00

How to get exactly 24 hours data from current timestamp

Posted: Fri Sep 14, 2007 12:22 am
by s.dot
You could use php to make a timestamp - mktime() or date()

Posted: Fri Sep 14, 2007 12:27 am
by sandy1028
scottayy wrote:You could use php to make a timestamp - mktime() or date()
Is it not possible in mysql query

Posted: Fri Sep 14, 2007 12:28 am
by Kieran Huggins
feyd wrote:Subtract a day from that time. It's generally held that there are 24 hours in a day, 60 minutes in a hour and 60 seconds in a minute.
Not in Canada, we're on metric time.

Posted: Fri Sep 14, 2007 12:52 am
by feyd
Kieran Huggins wrote:Not in Canada, we're on metric time.
True, that would be 3 hexahours a day, 48 decaminutes per hexahour, and 60 decaseconds per decaminute.

Posted: Fri Sep 14, 2007 1:08 am
by s.dot
sandy1028 wrote:
scottayy wrote:You could use php to make a timestamp - mktime() or date()
Is it not possible in mysql query
It is possible if the query is in a php script. If it is not, then ignore me. :)

Posted: Fri Sep 14, 2007 1:20 am
by Christopher
feyd wrote:
Kieran Huggins wrote:Not in Canada, we're on metric time.
True, that would be 3 hexahours a day, 48 decaminutes per hexahour, and 60 decaseconds per decaminute.
I think Canada has 10 deca-ehs in a day with 100 milli-ehs in a deca-eh. That's because a Canuck can polish off a two-four faster those hexa hosers.

Posted: Fri Sep 14, 2007 2:07 am
by mrkite
sandy1028 wrote:
select * from `table_name` where `fieldname` < now() and `fieldname` > now()-interval 1 day


But I am not getting exactly 24hours data.

I am getting the data from cur timestamp till previous day data till 2007-09-13 00:00:00

How to get exactly 24 hours data from current timestamp

It could be because you're using a bit of a non-standard way of doing your time manips.

Code: Select all

select * from table where field <= now() and field> date_sub(now(),interval 1 day)