CODEWITHSUNDEEP
mysql
Fuzzyma
Fuzzyma

Reputation: 8484

MySQL SEC_TO_TIME gives hh:mm:ss.000000

As said in the caption I am wondering why the SEC_TO_TIME-Function of MySQL gives me that Zeros at the end.

Refering to the docu (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_sec-to-time) that shouldn't happen (I am using MySQL 5.0.11).

Any Idea why this Zeros appears and how to get rid of them? To much zeros for displaying miliseconds.

Sine the zeros doens't break MySQLs Date-funcions, it's more a "I don't like that"-Question rather than a real Problem (at least till now^^)

// EDIT: I just figured out that the zeros aren't coming from the SEC_TO_TIME but from the FROM_UNIXTIME()-Function. Thx to @Abhik Chakraborty to ask for the input!

// EDIT2: I used FROM_UNIXTIME(last_try, '%Y-%m-%d %H:%i:%s') to get rid of the zeros. But When I do TIME(FROM_UNIXTIME(last_try, '%Y-%m-%d %H:%i:%s')) the zeros are back. Why??

Seems like every function adds the zeros back. Using SEC_TO_TIME on a simple integer-value also gives zeros...

Here is the whole query iam using: 

SELECT
    SEC_TO_TIME(FLOOR(TIME_TO_SEC((TIME(FROM_UNIXTIME(`last_try`))))/1800)*1800)
FROM `last48h`

The query reads the timestamp, gets only the time, converts it to seconds, breaks the seconds into half-hours (/1800 gives 0 < x < 48) rounds down and converts back to time

Upvotes: 1

Views: 4644

Answers (3)

Paweł Miłosz
Paweł Miłosz

Reputation: 109

Try to use TIME_FORMAT with %k specifier it should help.

Upvotes: 0

Jeremy N
Jeremy N

Reputation: 1

I had the same problem with the 'SEC_TO_TIME' function.

I had overlooked the fact that I was storing timestamps as a VARCHAR.

I changed my datatypes from VARCHAR to BIGINT and it is formatting the output values as expected (hh:mm:ss).

Upvotes: 0

O. Jones
O. Jones

Reputation: 108746

SEC_TO_TIME produces a TIME data type for its result. You can format that as you wish with DATE_FORMAT.

If you actually need subsecond time resolution you'll need to move to version 5.6.4 or beyond.

When you directly SELECT any sort of TIME data type to display, you get a default TIME-to-string conversion operation. The default TIME-to-string conversion in some generations of MySQL yields a string ending in hh:mm:ss+zz00. +zz00 is a timezone indicator, and often displays as +0000. Any chance that's what you're seeing?

It doesn't make sense to try to handle a UNIX_TIMESTAMP() style number of seconds using SEC_TO_TIME(). As of mid-2014 the current unix timestamp value is above 1.39 gigaseconds. TIME data types are used for stuff like elapsed times, and have a limit of just under 839 hours (3 megaseconds, precisely 3020399 seconds), and silently truncate their values.

For example, this is a good use of SEC_TO_TIME:

   SELECT SEC_TO_TIME(end_timestamp - start_timestamp) AS duration

edit Strangely enough, this query 

SELECT
  SEC_TO_TIME(FLOOR(TIME_TO_SEC((TIME(FROM_UNIXTIME(UNIX_TIMESTAMP()))))/1800)*1800) AS a,
  FLOOR(TIME_TO_SEC((TIME(FROM_UNIXTIME(UNIX_TIMESTAMP()))))/1800)*1800 AS b,
  TIME_TO_SEC((TIME(FROM_UNIXTIME(UNIX_TIMESTAMP()))))/1800 AS c,
  FROM_UNIXTIME(UNIX_TIMESTAMP()) AS d,
  FROM_UNIXTIME(UNIX_TIMESTAMP() - UNIX_TIMESTAMP() % 1800) as e

doesn't show any of the 0000 stuff through the phpmyadmin instance I use.

By the way, most people who round time to the nearest interval (a half-hour in your case) prefer to use a modulo and a subtraction; it's less dependent on implicit numerical type conversion than your method.

 SELECT TIME(FROM_UNIXTIME(last_try - last_try%1800))

does what the query in your question does.

Upvotes: 1

Related Questions