[client]
default-character-set=utf8
This option can force client programs to use specific character set.
This is normally unnecessary. However, when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. In such cases, starting the mysql client with --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the problem.
[mysqld]
default-character-set = utf8
MySQL Server has a server character set and a server collation. These can be set at server startup on the command line or in an option file and changed at runtime.
The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.
Conclusion: Client setting is at session level whenever client connects to server, server forces clients to use specific charset however this is not necessary.
Mysqld section charset indicates that server is configured to use particular charset by default however you can change charset at any level Database
,Table
and Column
level you want.
The answer lies in fully understanding the TIMEDIFF
function and what it returns as an integer.
First, let's consider TIMEDIFF
in its native element and what it returns:
mysql> select timediff('0:0:0','1:0:0') as timediff;
+-----------+
| timediff |
+-----------+
| -01:00:00 |
+-----------+
1 row in set (0.00 sec)
Second, what does TIMEDIFF
return in an integer context - done by adding zero:
mysql> select timediff('0:0:0','1:0:0')+0 as timediff2;
+--------------+
| timediff2 |
+--------------+
| 10000.000000 |
+--------------+
1 row in set (0.01 sec)
Now, we use the TIME_TO_SEC
function to return what we actually want:
mysql> select time_to_sec(timediff('0:0:0','1:0:0')+0)/60 as timediff3;
+-----------+
| timediff3 |
+-----------+
| 60.0000 |
+-----------+
1 row in set (0.00 sec)
The integer value returned by TIMEDIFF
is not minutes or seconds, but rather a base-10 representation of the actual time - so 01:00:00 becomes 10000, and 05:05:01 becomes 50501.
Instead of using TIME_TO_SEC
one can also use TIMESTAMPDIFF
instead of TIMEDIFF
and set the units to whatever is desired:
mysql> select timestampdiff(MINUTE,'2012-04-13 0:0:0','2012-04-13 1:0:0') as timediff4;
+-----------+
| timediff4 |
+-----------+
| 60 |
+-----------+
1 row in set (0.00 sec)
Instead of MINUTE
, you can also use FRAC_SECOND
(microseconds), SECOND
, MINUTE
, HOUR
, DAY
, WEEK
, MONTH
, QUARTER
, or YEAR
.
The time functions in MySQL (in this case, version 5.1) are all described in the manual. The online manual also links to other versions (such as MySQL 5.5) as well.
Best Answer
Your situation is simple. You're trying to format a time result that is
00:08:42
and according with the MySQL date and time functions,%h
forHour (01..12)
, you're converting00
(hour) to12
. If you change it for%H
you will get the desired result%H
forHour (00..23)
.Try this test:
Try it in SQLFiddle.