?
Solved

mysql timestamp ceiling to 5 minutes

Posted on 2004-11-23
3
Medium Priority
?
597 Views
Last Modified: 2006-11-17
Hai,

Is there any function ( or an efficient way) to get the following functionality in mysql.

2005-11-10 22:31:12 as 2005-11-10 22:35
2005-11-10 22:34:13 as 2005-11-10 22:35
2005-11-10 22:43:13 as 2005-11-10 22:45
2005-11-10 22:47:13 as 2005-11-10 22:50.

Its nothing but a ceiling based on 5 minutes.

Please help.

regards,
MSKumar
0
Comment
Question by:mskumar_apk
3 Comments
 
LVL 20

Assisted Solution

by:virmaior
virmaior earned 150 total points
ID: 12665706
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html provides the majority of things that would help here..
I'm not seeing any rounding functions

but try this:

(as if your field was called mytime)
SELECT CONCAT(EXTRACT(YEAR_MONTH, mytime) , EXTRACT(DAY_HOUR,mytime),(EXTRACT(MINUTE,mytime) DIV 5)*5 ) FROM mytable

the use of the integer DIV operator makes this require MySQL 4.1

SELECT CONCAT(EXTRACT(YEAR_MONTH, mytime) , EXTRACT(DAY_HOUR,mytime),(CEIL(EXTRACT(MINUTE,mytime) / 5)*5 )) FROM mytable

which should work in any v4 or 3.23+
0
 

Accepted Solution

by:
PetrTomenendal earned 225 total points
ID: 12667823
or you can try this:
select *,FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(datum)/300)*300,'%Y-%m-%d %H:%i') from test;
0
 

Author Comment

by:mskumar_apk
ID: 12679049
Hai,

thanks for ur reply. both works well. splitted the points. once again thanks for ur help.

regards,
MSKumar
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question