時間資料對資料處理來說,佔有重要的地位.

幾種常用時間單位相互轉換,是常見的問題.

以下以MySQL為例,利用函數,做示範:

CREATE TABLE ithelp1001a(
ts  TIME NOT NULL
);

INSERT INTO ithelp1001a(ts) VALUES
('16:00:00'),
('07:02:34'),
('28:34:59');

SELECT ts
     , TIME_TO_SEC(ts)
     , SEC_TO_TIME(TIME_TO_SEC(ts))
  FROM ithelp1001a;

+----------+-----------------+------------------------------+
| ts       | TIME_TO_SEC(ts) | SEC_TO_TIME(TIME_TO_SEC(ts)) |
+----------+-----------------+------------------------------+
| 16:00:00 |           57600 | 16:00:00                     |
| 07:02:34 |           25354 | 07:02:34                     |
| 28:34:59 |          102899 | 28:34:59                     |
+----------+-----------------+------------------------------+

SELECT ts
     , TIME_TO_SEC(ts) AS '秒數'
     , TIME_TO_SEC(ts)/60 AS '分鐘數'
     , TIME_TO_SEC(ts)/3600 AS '小時數'
     , TIME_TO_SEC(ts)/86400 AS '天數'
  FROM ithelp1001a;

+----------+--------+-----------+-----------+--------+
| ts       | 秒數   | 分鐘數    | 小時數    | 天數   |
+----------+--------+-----------+-----------+--------+
| 16:00:00 |  57600 |  960.0000 |   16.0000 | 0.6667 |
| 07:02:34 |  25354 |  422.5667 |    7.0428 | 0.2934 |
| 28:34:59 | 102899 | 1714.9833 |   28.5831 | 1.1910 |
+----------+--------+-----------+-----------+--------+