timestamp

[sandy1028]

How to query for one day interval and exclude the seconds of timestamp

If the current timestamp is $time='2008-08-13 14:00:02'; and if for the previous day for the 14hrs if the timestamp updated as '2008-08-13 14:00:01'. The data at the this '2008-08-12 14:00:01' is excluded when queried as below.

So how to avoid it by using the query excluding seconds in the timestamp and considering only '2008-08-13 14:00' from the timestamp.

select timestamp, fieldname from table where timestamp <= ('".$time."') and timestamp >= date_sub('".$time."',interval 1 day) order by timestamp

[kato_san]

how about this?

$time = date('Y-m-d h:i:s',(floor ( (time () / 60) )) * 60);

kato

[Sxooter]

I prefer date_trunc:

select date_trunc('minute',timestamp);

myself. Plus you can index on it:

create index table_ts_by_min on table (date_trunc('minute',timestampfield));

and then group by, order by, and where clauses can use an index on it.

However, I'm speaking of things that work in the database in my signature. Not sure which one you're running as you didn't mention it.

[sandy1028]

Hi,

Actually if I use date(timestamp)=date(now()) is for matching the date and current date. In similar way how to match only the format YYYY-mm-dd hr:min
and not seconds.

If I use the query as below
SELECT timestamp from tablename where substring(timestamp,1,16) <= ('2008-08-18 11:20') and substring(timestamp,1,16) >= date_sub('2008-08-18 11:20',interval 1 day) group by timestamp order by timestamp;

The output doesnot fetch the previous day's data at timestamp 2008-08-18 11:20 even if it present.