0

I have a seconds column and I need to display in some readable format.

Table A
Time
259200

select Time from Table A

and I need to get : 3 days,00:00:00 I need to have no of days if > 24 hrs.

I tried lots of way but I was not able to get the right answer.

A few things which I tried are:

select  floor(hour( sec_to_time
Select sec_to_time
SELECT TIME_FORMAT( MAKETIME(
Alex K
  • 22,315
  • 19
  • 108
  • 236
user2111815
  • 151
  • 1
  • 2
  • 10

2 Answers2

1

How about this:

SELECT 
    CONCAT_WS(' ', 
              `time` DIV 86400, 'day(s) and', 
              SEC_TO_TIME(MOD(`time`, 86400)), 'hours'
             ) 
FROM TableName;

time DIV 86400 gives you the number of day(s), SEC_TO_TIME(MOD(``time``, 86400)) gives you the h:m:s remaining. Then just concat them.

Output:

0 day(s) and 01:50:00 hours
1 day(s) and 10:17:36 hours
101 day(s) and 10:50:32 hours

plz see sql fiddle.

jurgenreza
  • 5,856
  • 2
  • 25
  • 37
  • +1 for the indentation of and the line breaks in the query. SQL is a language too! – 11684 Apr 07 '13 at 18:26
  • Thanks Jurgenreza U R Awesome, It works in my sql through command prompt but through sql workbench it gives BLOB and i am using this value in Ireport and even there it prints blob :( any inputs will be a great help – user2111815 Apr 07 '13 at 19:58
  • @user2111815 BLOB problem is not related to mysql. see this post on how to fix it: http://stackoverflow.com/questions/10713397/mysql-groupconcat-returning-blob-xxxb-kib – jurgenreza Apr 07 '13 at 21:55
  • @user2111815 Click on the check mark on the left side under the score. :) – jurgenreza Apr 07 '13 at 23:49
0

Check the MySQL manual for time and date functions. For example, SEC_TO_TIME and TO_DAYS.

UPDATE:

Quite right, I misread. The following is a terrible hack but should give you the answer you want. For a real system, I would write a proper function for this.

concat(floor(hour(sec_to_time(x))/24), " days, ", subtime(sec_to_time(x),concat(floor(hour(sec_to_time(x))/24)*24,":00:00")))
igelkott
  • 1,287
  • 8
  • 9