iT邦幫忙

1

mysql / ms sql -儲存年月時間的欄位最佳方式??

各位好:
這個問題我思考很久,
想跟各位高手請教:

目前需要有0000-00-00 00-00-00這樣的格式當作年月日小時來儲存。
EX:

2015-08-30 20:12:00
2015-08-31 20:12:00
...

雖然也可以使用strtotime來轉成int整數型態儲存,但不會用此方式。
已經確認會直接儲存完整的日期文字型態,也就是DATETIME型態。

但是如果考量到如果有時常需要對此日期欄位搜尋的話,
哪種方式會比較好呢??

方式1: 日期年月日、時間單一欄位儲存

---------------------------------------------
title     cont   vent       save_at
---------------------------------------------
AAgfS    DWDFWF    1    2015-06-01 10:32:00
VAtS     FDFFWF    3    2015-06-01 11:32:50
bncvtS   BVDFFWF   1    2015-06-01 13:52:30
bASS     EFWF      2    2015-06-02 09:33:33
bncvtS   BVDFFWF   1    2015-06-03 07:42:22
bASS     EFWF      2    2015-06-04 12:37:00
---------------------------------------------

方式2: 日期年月日、時間拆成欄位儲存

--------------------------------------------------------
title     cont   vent     save_at_y     save_at_h
--------------------------------------------------------
AAgfS    DWDFWF    1    2015-06-01      10:32:00
VAtS     FDFFWF    3    2015-06-01      11:32:50
bncvtS   BVDFFWF   1    2015-06-01      13:52:30
bASS     EFWF      2    2015-06-02      09:33:33
bncvtS   BVDFFWF   1    2015-06-03      07:42:22
bASS     EFWF      2    2015-06-04      12:37:00
--------------------------------------------------------

假設需要常常下SQL語法查詢某區間,
通常只會用年月日,但不會包含時間,

SELECT * FROM [table] WHERE save_at BETWEEN [2015-06-01] AND [2015-06-01]

==查詢介面====
請選擇兩日期:2015-06-01 到 2015-06-01

方式1跟方式2哪種效率比較好呢?
此[table]超過800萬筆資料的情況下...

如果用方式 1 那不就還要自己組裝時間的部分才能找到??例如幫它補上00:00:00跟23:59:59:

SELECT * FROM [table] WHERE save_at BETWEEN [2015-06-01 00:00:00] AND [2015-06-01 23:59:59]
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
2
allenlwh
iT邦高手 1 級 ‧ 2015-08-31 09:32:06
最佳解答

在ms-msql下,可以使用

<pre class="c" name="code">SELECT * FROM [table] WHERE convert(varchar(10),save_at,112)='20150601'
isthome iT邦新手 4 級 ‧ 2015-09-01 16:20:22 檢舉

您好:感謝提供語法

2
wiseguy
iT邦超人 1 級 ‧ 2015-08-31 09:26:15

isthome提到:
0000-00-00 00-00-00

mysql 的 datetime 欄位,可以使用 0000-00-00 00:00:00 這種內容。
Illegal DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

isthome iT邦新手 4 級 ‧ 2015-09-01 15:08:58 檢舉

您好:感謝您的建議,已經確認會直接儲存完整的日期文字型態,也就是DATETIME型態

0
窮嘶發發發
iT邦高手 1 級 ‧ 2015-08-31 09:34:00

設計系統的大部分都會用 方式 1
因為方式 2 SELECT 的時候要用到遞迴查詢
有些人對這個不太熟怎麼作反而會覺得麻煩

如果你對遞迴查詢很熟,那用方式2 比較好
而且以效能來說,方式2 優於 方式1
尤其是你有八百萬筆資料的狀況下

個人是建議你應該要照年份切資料表的
你的 SQL 應該是 MS 或是 ORACLE 的
如果是 MY 的基本上 一萬筆就要切了,不可能到八百萬的
就算是買商業版,硬體效能不錯,不然單表 八百萬,真的有點多

isthome iT邦新手 4 級 ‧ 2015-09-01 15:21:23 檢舉

您好:
感謝回覆,不過您提的有幾點想再跟您請教:

jones888提到:
如果你對遞迴查詢很熟,那用方式2 比較好
而且以效能來說,方式2 優於 方式1

我也在想如果是開放查詢格式都是[YYYY-MM-DD]~[YYYY-MM-DD]的情況,而不是[YYYY-MM-DD hh:mm:ss]~[YYYY-MM-DD hh:mm:ss]的情況下,
用方式2: 日期年月日、時間拆成欄位儲存
直接掃那個表的欄位是最快的。

但是不懂何謂是『遞迴查詢』??


但是如果用方式2,萬一要開放查詢[YYYY-MM-DD hh:mm:ss]的格式,
好像也是要組裝一下欄位??

例如要查出介於 2015-06-01 的 08:30~12:00之間的資料,構想語法:

<pre class="c" name="code">
SELECT * FROM [table] WHERE  [save_at_y] + [save_at_h] BETWEEN [2015-06-01 08:30:00] AND [2015-06-01 12:00:00]
isthome iT邦新手 4 級 ‧ 2015-09-01 16:18:24 檢舉

尤其是你有八百萬筆資料的狀況下

個人是建議你應該要照年份切資料表的
你的 SQL 應該是 MS 或是 ORACLE 的
就算是買商業版,硬體效能不錯,不然單表 八百萬,真的有點多

目前是使用mysql,
嚴格上來說,
已經是10,284,203筆了...

的確,會有瀏覽時讀取會越來越慢的情況...

如果是 MY 的基本上 一萬筆就要切了,不可能到八百萬的

基本上有往您說的這方面去考量,照年份切資料表,
讓程式自己去依照年度,建立相關的schema,
但是一想到之後如果要查合併年度的,
SQL語法如何使用,這又得傷腦筋了??!!

Albert iT邦高手 1 級 ‧ 2015-09-01 21:03:29 檢舉

會計結算很多是分資料表存放
我們都是依據選擇不同區段到不同資料表中去抓取資料
你都自己寫很用功

我要發表回答

立即登入回答