Joinutility seperatorLogin utility separator Infobright.com
   
 
time_to_sec mysql function
 
nmittal
Posted: 17 March 2010 10:42 AM   [ Ignore ]  
Newbie
Rank
Total Posts:  5
Joined  2010-03-16

I have a table with a time field. I have used time_to_sec function to convert the time to seconds from midnight. I need this to create 5 min buckets for data…

select
sec_to_time((floor(time_to_sec(time)/300)+1)*300) as time_bucket,
count(*)
from
trades
where
date=“20090908”
group by time_bucket
order by time_bucket

1) I dont see time_to_sec & sec_to_time in the list of optimized functions for Infobright. So does that mean my query is running slow (it takes 70 seconds for about 25M records)
2) is there a better way I can write the above query?

thanks for your help in advance.

Nishant

Profile
 
Janusz Borkowski
Posted: 17 March 2010 11:07 AM   [ Ignore ]   [ # 1 ]  
Super Duper Member
Avatar
RankRankRankRankRank
Total Posts:  916
Joined  2008-08-18

Hi !

You can refer to http://dev.mysql.com/doc/refman/5.1/en/func-op-summary-ref.html for the list of functions which should work fine in Infobright. time_to_sec and sec_to_time are there.

The query would be much faster if the buckets are created in ETL, so the table contains a column on which you can directly group by.

Profile
 
nmittal
Posted: 17 March 2010 11:14 AM   [ Ignore ]   [ # 2 ]  
Newbie
Rank
Total Posts:  5
Joined  2010-03-16

Thanks.
Problem is that bucket size is user configurable (so it could be 10 mins or 5 secs) so I cannot have it as a column.

thanks for your response.

Profile
 
David Lutz
Posted: 17 March 2010 12:55 PM   [ Ignore ]   [ # 3 ]  
Sr. Member
Avatar
RankRankRankRank
Total Posts:  738
Joined  2008-08-18

nmittal,

To confirm that the time_to_sec() function is supported in Infobright see the following session information.  Note that no warning is issued by Infobright when using the function with table/column data in a brighthouse table.

[root@localhost ~]# mysql-ib 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log build number (revision)=IB_3.3.2_r7501_7556(iee - commercial)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table nmittal (col1 time not null) engine=brighthouse;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into nmittal values('12:12:12');
Query OK, 1 row affected (0.11 sec)

mysql> select * from nmittal;
+----------+
| col1     |
+----------+
| 12:12:12 |
+----------+
1 row in set (0.00 sec)

mysql> select time_to_sec('12:12:12');
+-------------------------+
| time_to_sec('12:12:12') |
+-------------------------+
|                   43932 |
+-------------------------+
1 row in set (0.02 sec)

mysql> select time_to_sec(col1) from nmittal;
+-------------------+
| time_to_sec(col1) |
+-------------------+
|             43932 |
+-------------------+
1 row in set (0.01 sec)

mysql> 
Signature 
Profile
 
nmittal
Posted: 18 March 2010 09:28 AM   [ Ignore ]   [ # 4 ]  
Newbie
Rank
Total Posts:  5
Joined  2010-03-16

Thanks David, both functions are working.

Profile
 
   
 
 
‹‹ Problems with running a Stored Function      infobright page allocation failure and corruption after server crash ››