timestamp

[rashmi_k28]

Hi,

 

I have a query like this

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

 

All the records between these are fetched due to seconds in the timestamp.

 

The timestamp is difference of 10 minutes and seconds can be 01 or 02 so on.

 

Due to this seconds difference the value fetched is less by 1 for the day.

How to exclude the seconds and query for the 1 day interval difference

 

[fenway]

I'm not sure what you mean... but you'd need to re-cast the field to DATETIME without seconds -- or, equivalently, round to the nearest minute (with a MOD 60).

[rashmi_k28]

When I query the timestamp

| 2008-08-08 06:40:01 |

| 2008-08-08 06:50:01 |

| 2008-08-08 07:00:02 |

| 2008-08-08 07:10:01 |

| 2008-08-08 07:20:01 |

| 2008-08-08 07:30:02 |

| 2008-08-08 07:40:01 |

| 2008-08-08 07:50:01 |

| 2008-08-08 08:00:02 |

| 2008-08-08 08:10:02 |

| 2008-08-08 08:20:05 |

| 2008-08-08 08:30:01 |

| 2008-08-08 08:40:01 |

| 2008-08-08 08:50:05 |

| 2008-08-08 09:00:02 |

| 2008-08-08 09:10:01 |

| 2008-08-08 09:20:01 |

| 2008-08-08 09:30:01 |

| 2008-08-08 09:40:02 |

| 2008-08-08 09:50:01 |

| 2008-08-08 10:00:01 |

| 2008-08-08 10:10:01 |

+---------------------+

 

But previous date timestamp may differ with the seconds

2008-08-07 09:10:01 |

| 2008-08-07 09:20:01 |

| 2008-08-07 09:30:01 |

| 2008-08-07 09:40:01 |

| 2008-08-07 09:50:01 |

| 2008-08-07 10:00:01 |

| 2008-08-07 10:10:01 |

| 2008-08-07 10:20:01 |

| 2008-08-07 10:30:01 |

| 2008-08-07 10:40:01 |

| 2008-08-07 10:50:01 |

| 2008-08-07 11:00:02 |

| 2008-08-07 11:10:01 |

| 2008-08-07 11:20:01 |

| 2008-08-07 11:30:01 |

| 2008-08-07 11:40:01 |

| 2008-08-07 11:50:02 |

| 2008-08-07 12:00:01 |

| 2008-08-07 12:10:01 |

| 2008-08-07 12:20:01 |

 

 

But When I use this timestamp to query the data from table,

Due to the seconds mistach the first value is missed out.

So how can i avoid it using the query excluding the seconds

If i use timestamp as 2008-08-07 12:20 then last value is missed out. It is considered as 2008-08-07 12:20:00

 

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

 

 

 

 

[fenway]

But When I use this timestamp to query the data from table,

Due to the seconds mistach the first value is missed out.

So how can i avoid it using the query excluding the seconds

I told you how to do this... have you tried?

[rashmi_k28]

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.

[fenway]

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.

Nor should it.  Just always round up, then MOD 60, then FROM_UNIXTIME().