CODEWITHSUNDEEP
mysqltimestamproundingminute
user985366
user985366

Reputation: 1699

MySQL : Select on timestamp column, change seconds to 00 and round minutes down to nearest 10

I have a table with a timestamp column as this:

2013-04-05 22:33:50

2013-04-05 22:42:03

2013-04-05 22:42:03

I want to make a SELECT on it and receive results with seconds 00 and minutes rounded down to nearest 10. As:

2013-04-05 22:30:00

2013-04-05 22:40:00

2013-04-05 22:40:00

How is this done? I cannot update or insert on the table, only select.

Upvotes: 1

Views: 2228

Answers (1)

Ryan Kempt
Ryan Kempt

Reputation: 4209

Here you go:

SELECT DATE_SUB(DATE_SUB(ticktick, INTERVAL MOD(MINUTE(ticktick),10) MINUTE), INTERVAL SECOND(ticktick) SECOND) FROM `table`

Upvotes: 4

Related Questions