Database Reference
In-Depth Information
RETURN
CONCAT
(
LEFT
(
TIME_FORMAT
(
t
,
'%r'
),
9
),
IF
(
TIME_TO_SEC
(
t
)
<
12
*
60
*
60
,
'a.m.'
,
'p.m.'
));
Use the function like this:
mysql>
SELECT t1, time_ampm(t1) FROM time_val;
+----------+---------------+
| t1 | time_ampm(t1) |
+----------+---------------+
| 15:00:00 | 03:00:00 p.m. |
| 05:01:30 | 05:01:30 a.m. |
| 12:30:20 | 12:30:20 p.m. |
+----------+---------------+
For more information about writing stored functions, see
Chapter 9
.
6.4. Setting the Client Time Zone
Problem
You have a client application that connects from a time zone different from the server.
Consequently, when it stores
TIMESTAMP
values, they don't have the correct UTC values.
Solution
The client should set the
time_zone
system variable after connecting to the server.
Discussion
Time zone settings have an important effect on
TIMESTAMP
values:
• When the MySQL server starts, it examines its operating environment to determine
its time zone. (To use a different value, start the server with the
--default-time-
zone
option.)
• For each client that connects, the server interprets
TIMESTAMP
values with respect
to the time zone associated with the client session. When a client inserts a
TIME
STAMP
value, the server converts it from the client time zone to UTC and stores the
UTC value. (Internally, the server stores a
TIMESTAMP
value as the number of seconds
since
1970-01-01
00:00:00
UTC.) When the client retrieves a
TIMESTAMP
value,
the server performs the reverse operation to convert the UTC value back to the
client time zone.
• The default session time zone for each client when it connects is the server time
zone. If all clients are in the same time zone as the server, nothing special need be
done for proper
TIMESTAMP
time zone conversion to occur. But if a client is in a time
