数据库里的时间格式 sql数据库中的时间类型
\u6570\u636e\u5e93\u4e2d \u65e5\u671f\u7684\u51e0\u79cd\u8868\u793a\u65b9\u6cd5Mysql\u4e2d\u7ecf\u5e38\u7528\u6765\u5b58\u50a8\u65e5\u671f\u7684\u6570\u636e\u7c7b\u578b\u6709\u4e09\u79cd\uff1aDate\u3001Datetime\u3001Timestamp\u3002
\u30101\u3011Date\u6570\u636e\u7c7b\u578b\uff1a\u7528\u6765\u5b58\u50a8\u6ca1\u6709\u65f6\u95f4\u7684\u65e5\u671f\u3002Mysql\u83b7\u53d6\u548c\u663e\u793a\u8fd9\u4e2a\u7c7b\u578b\u7684\u683c\u5f0f\u4e3a\u201cYYYY-MM-DD\u201d\u3002\u652f\u6301\u7684\u65f6\u95f4\u8303\u56f4\u4e3a\u201c1000-00-00\u201d\u5230\u201c9999-12-31\u201d\u3002
\u30102\u3011Datetime\u7c7b\u578b\uff1a\u5b58\u50a8\u65e2\u6709\u65e5\u671f\u53c8\u6709\u65f6\u95f4\u7684\u6570\u636e\u3002\u5b58\u50a8\u548c\u663e\u793a\u7684\u683c\u5f0f\u4e3a \u201cYYYY-MM-DD HH:MM:SS\u201d\u3002\u652f\u6301\u7684\u65f6\u95f4\u8303\u56f4\u662f\u201c1000-00-00 00:00:00\u201d\u5230\u201c9999-12-31 23:59:59\u201d\u3002
\u30103\u3011Timestamp\u7c7b\u578b\uff1a\u4e5f\u662f\u5b58\u50a8\u65e2\u6709\u65e5\u671f\u53c8\u6709\u65f6\u95f4\u7684\u6570\u636e\u3002\u5b58\u50a8\u548c\u663e\u793a\u7684\u683c\u5f0f\u8ddfDatetime\u4e00\u6837\u3002\u652f\u6301\u7684\u65f6\u95f4\u8303\u56f4\u662f\u201c1970-01-01 00:00:01\u201d\u5230\u201c2038-01-19 03:14:07\u201d\u3002
\u6269\u5c55\u8d44\u6599
\u5207\u8bb0\u4e0d\u8981\u7528\u5b57\u7b26\u4e32\u5b58\u50a8\u65e5\u671f
\u8fd9\u79cd\u5b58\u50a8\u65e5\u671f\u7684\u65b9\u5f0f\u7684\u4f18\u70b9\u8fd8\u662f\u6709\u7684\uff0c\u5c31\u662f\u7b80\u5355\u76f4\u767d\uff0c\u5bb9\u6613\u4e0a\u624b\u3002\u4f46\u662f\uff0c\u8fd9\u662f\u4e0d\u6b63\u786e\u7684\u505a\u6cd5\uff0c\u4e3b\u8981\u4f1a\u6709\u4e0b\u9762\u4e24\u4e2a\u95ee\u9898\uff1a
1\u3001\u5b57\u7b26\u4e32\u5360\u7528\u7684\u7a7a\u95f4\u66f4\u5927\u3002
2\u3001\u5b57\u7b26\u4e32\u5b58\u50a8\u7684\u65e5\u671f\u6bd4\u8f83\u6548\u7387\u6bd4\u8f83\u4f4e\uff08\u9010\u4e2a\u5b57\u7b26\u8fdb\u884c\u6bd4\u5bf9\uff09\uff0c\u65e0\u6cd5\u7528\u65e5\u671f\u76f8\u5173\u7684 API \u8fdb\u884c\u8ba1\u7b97\u548c\u6bd4\u8f83\u3002
DateTime \u7c7b\u578b\u6ca1\u6709\u65f6\u533a\u4fe1\u606f\u7684
DateTime \u7c7b\u578b\u662f\u6ca1\u6709\u65f6\u533a\u4fe1\u606f\u7684\uff08\u65f6\u533a\u65e0\u5173\uff09 \uff0cDateTime \u7c7b\u578b\u4fdd\u5b58\u7684\u65f6\u95f4\u90fd\u662f\u5f53\u524d\u4f1a\u8bdd\u6240\u8bbe\u7f6e\u7684\u65f6\u533a\u5bf9\u5e94\u7684\u65f6\u95f4\u3002
\u5f53\u4f60\u7684\u65f6\u533a\u66f4\u6362\u4e4b\u540e\uff0c\u6bd4\u5982\u4f60\u7684\u670d\u52a1\u5668\u66f4\u6362\u5730\u5740\u6216\u8005\u66f4\u6362\u5ba2\u6237\u7aef\u8fde\u63a5\u65f6\u533a\u8bbe\u7f6e\u7684\u8bdd\uff0c\u5c31\u4f1a\u5bfc\u81f4\u4f60\u4ece\u6570\u636e\u5e93\u4e2d\u8bfb\u51fa\u7684\u65f6\u95f4\u9519\u8bef\u3002\u4e0d\u8981\u5c0f\u770b\u8fd9\u4e2a\u95ee\u9898\uff0c\u5f88\u591a\u7cfb\u7edf\u5c31\u662f\u56e0\u4e3a\u8fd9\u4e2a\u95ee\u9898\u95f9\u51fa\u4e86\u5f88\u591a\u7b11\u8bdd\u3002
Timestamp \u548c\u65f6\u533a\u6709\u5173\u3002Timestamp \u7c7b\u578b\u5b57\u6bb5\u7684\u503c\u4f1a\u968f\u7740\u670d\u52a1\u5668\u65f6\u533a\u7684\u53d8\u5316\u800c\u53d8\u5316\uff0c\u81ea\u52a8\u6362\u7b97\u6210\u76f8\u5e94\u7684\u65f6\u95f4\uff0c\u8bf4\u7b80\u5355\u70b9\u5c31\u662f\u5728\u4e0d\u540c\u65f6\u533a\uff0c\u67e5\u8be2\u5230\u540c\u4e00\u4e2a\u6761\u8bb0\u5f55\u6b64\u5b57\u6bb5\u7684\u503c\u4f1a\u4e0d\u4e00\u6837\u3002
\u65e5\u671f\u65f6\u95f4\u7c7b\u578b\uff1a
time\uff1a\u683c\u5f0f\uff1ahh:mm:ss[.nnnnnnn]
\u8303\u56f4\uff1a00:00:00.0000000 \u523023:59:59.9999999
\u7cbe\u786e\u5ea6\uff1a100 \u7eb3\u79d2
\u5b58\u50a8\u5927\u5c0f\uff08\u4ee5\u5b57\u8282\u4e3a\u5355\u4f4d\uff09\uff1a3-5
\u7528\u6237\u5b9a\u4e49\u7684\u79d2\u7684\u5c0f\u6570\u7cbe\u5ea6\uff1a\u6709
\u65f6\u533a\u504f\u79fb\u91cf\uff1a\u65e0
date\uff1a
\u683c\u5f0f\uff1aYYYY-MM-DD
\u8303\u56f4\uff1a0001-01-01 \u5230 9999-12-31
\u7cbe\u786e\u5ea6\uff1a1\u5929
\u5b58\u50a8\u5927\u5c0f\uff08\u4ee5\u5b57\u8282\u4e3a\u5355\u4f4d\uff09\uff1a3
\u7528\u6237\u5b9a\u4e49\u7684\u79d2\u7684\u5c0f\u6570\u7cbe\u5ea6\uff1a\u65e0
\u65f6\u533a\u504f\u79fb\u91cf\uff1a\u65e0
smalldatetime\uff1a
\u683c\u5f0f\uff1aYYYY-MM-DD hh:mm:ss
\u8303\u56f4\uff1a1900-01-01 \u5230 2079-06-06
\u7cbe\u786e\u5ea6\uff1a1\u5206\u949f
\u5b58\u50a8\u5927\u5c0f\uff08\u4ee5\u5b57\u8282\u4e3a\u5355\u4f4d\uff09\uff1a4
\u7528\u6237\u5b9a\u4e49\u7684\u79d2\u7684\u5c0f\u6570\u7cbe\u5ea6\uff1a\u65e0
\u65f6\u533a\u504f\u79fb\u91cf\uff1a\u65e0
datetime\uff1a
\u683c\u5f0f\uff1aYYYY-MM-DD hh:mm:ss[.nnn]
\u8303\u56f4\uff1a1753-01-01 \u5230 9999-12-31
\u7cbe\u786e\u5ea6\uff1a0.00333\u79d2
\u5b58\u50a8\u5927\u5c0f\uff08\u4ee5\u5b57\u8282\u4e3a\u5355\u4f4d\uff09\uff1a8
\u7528\u6237\u5b9a\u4e49\u7684\u79d2\u7684\u5c0f\u6570\u7cbe\u5ea6\uff1a\u65e0
\u65f6\u533a\u504f\u79fb\u91cf\uff1a\u65e0
datetime2\uff1a
\u683c\u5f0f\uff1aYYYY-MM-DD hh:mm:ss[.nnnnnnn]
\u8303\u56f4\uff1a0001-01-01 00:00:00.0000000 \u52309999-12-31 23:59:59.9999999
\u7cbe\u786e\u5ea6\uff1a100\u94a0\u79d2
\u5b58\u50a8\u5927\u5c0f\uff08\u4ee5\u5b57\u8282\u4e3a\u5355\u4f4d\uff09\uff1a6 \u5230 8
\u7528\u6237\u5b9a\u4e49\u7684\u79d2\u7684\u5c0f\u6570\u7cbe\u5ea6\uff1a\u6709
\u65f6\u533a\u504f\u79fb\u91cf\uff1a\u65e0
datetimeoffset\uff1a
\u683c\u5f0f\uff1aYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
\u8303\u56f4\uff1a0001-01-01 00:00:00.0000000 \u52309999-12-31 23:59:59.9999999\uff08\u4ee5UTC \u65f6\u95f4\u8868\u793a\uff09
\u7cbe\u786e\u5ea6\uff1a100\u94a0\u79d2
\u5b58\u50a8\u5927\u5c0f\uff08\u4ee5\u5b57\u8282\u4e3a\u5355\u4f4d\uff09\uff1a8 \u523010
\u7528\u6237\u5b9a\u4e49\u7684\u79d2\u7684\u5c0f\u6570\u7cbe\u5ea6\uff1a\u6709
\u65f6\u533a\u504f\u79fb\u91cf\uff1a\u6709
SQL> select sysdate from dual;
SYSDATE
------------
17-JUN-15
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2015-06-17 14:08:21
方法二：更改会话参数
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
方法二：更改参数文件
可以在 init.ora 中加上一行
nls_date_format='yyyy-mm-dd hh24:mi:ss'
或者
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss’ scope=spfile;
--1970年到现在时间的秒数?
--mssql:
select dateadd(second,1434506400,'1970-01-01 08:00:00')
--oracle:
select to_date('1970-01-01 08:00:00','YYYY-MM-DD HH24:MI:SS') +1434506400/24/3600 from dual
绛旓細鏃ユ湡鏃堕棿绫诲瀷锛歵ime锛鏍煎紡锛歨h:mm:ss[.nnnnnnn]鑼冨洿锛00:00:00.0000000 鍒23:59:59.9999999 绮剧‘搴︼細100 绾崇 瀛樺偍澶у皬锛堜互瀛楄妭涓哄崟浣嶏級锛3-5 鐢ㄦ埛瀹氫箟鐨勭鐨勫皬鏁扮簿搴︼細鏈 鏃跺尯鍋忕Щ閲忥細鏃 date锛氭牸寮忥細YYYY-MM-DD 鑼冨洿锛0001-01-01 鍒 9999-12-31 绮剧‘搴︼細1澶 瀛樺偍澶у皬锛堜互瀛楄妭涓哄崟浣嶏級锛...
绛旓細3銆佽繖鏍峰氨浼氳繘鍏ュ埌浜嗙殑鎶ヨ〃鐨勭晫闈㈠綋涓紝淇敼鍙充晶涓鏃堕棿鏃ユ湡銆4銆佺偣鍑荤殑鑿滃崟涓殑 鏃ユ湡鍜屾椂闂 鐨勯夐」銆5銆佽繖鏍峰氨浼氬脊鍑轰簡涓涓棩鏈熷拰鏃堕棿绐楀彛褰撲腑锛岄変腑闇瑕佽缃殑鏃堕棿鐨勯夐」銆6銆鏃堕棿鐨勬牸寮鐨勯変腑瀹屾垚涔嬪悗锛岀偣鍑 纭畾淇濆瓨璁剧疆銆7銆佸彲浠ョ湅鍒版槸鍦ㄥ彸渚т腑鏄剧ず浜嗕慨鏀瑰畬鎴愪簡銆傛敞鎰忎簨椤癸細access鏈夊己澶х殑鏁版嵁澶勭悊銆...
绛旓細鍦ㄦ暟鎹簱涓鏃堕棿鏍煎紡鏄噰鐢║TC鏃堕棿鐨勩2010-1-1銆2010-11-1銆2010-1-11杩欎簺閮芥槸姝ｇ‘鐨勶紝娌℃湁浠讳綍閿欒銆備綘娣诲姞鍒鏁版嵁搴撲腑鐨鏃跺欐敞鎰忔墦鈥-鈥濆皢鏉ュ湪澶栭儴搴旂敤绋嬪簭涓綘鎯宠鏄剧ず涓轰粈涔堟牸寮忥紝閭ｄ釜浣犲彲浠ヨ嚜宸辨牸寮忓寲鍗冲彲銆
绛旓細sql2000榛樿鐨刣atetime绫诲瀷鏄剧ず鏍煎紡濡傚浘銆傞儴鍒嗙増鏈細鏈夊樊鍒係QL鐨刢onvert鍑芥暟鍙互鎸夐渶姹傝浆鎹㈡樉绀烘牸寮忋侰ONVERT(data_type(length),data_to_be_converted,style)data_type(length) 瑙勫畾鐩爣鏁版嵁绫诲瀷锛堝甫鏈夊彲閫夌殑闀垮害锛夈俤ata_to_be_converted 鍚湁闇瑕佽浆鎹㈢殑鍊笺俿tyle 瑙勫畾鏃ユ湡/鏃堕棿鐨勮緭鍑烘牸寮忋備互getdate()...
绛旓細姣斿涓崍12鐐规暣灏辨槸0.5锛夛紝鍥犳 1999骞12鏈30鏃5鏃38鍒47绉 杩欎釜鏃堕棿鍦鏁版嵁搴撲腑鐨瀹為檯瀛樺偍鏁版嵁涓36524.2352662037銆傜敤鍙岀簿搴︽诞鐐规暟璁板綍鏃堕棿鍙互璁拌浇鍑犲崄涓囧勾鐨勬椂闂锛屾墍浠ヤ綘鍦ㄨ緭鍏ユ椂闂寸殑鏃跺欏畬鍏ㄦ棤闇鎷呭績浼氳秴鍑洪檺鍒剁殑闂锛屼綘鍙渶鎸夌収鎸囧畾鐨鏍煎紡杈撳叆鍗冲彲锛岀郴缁熶細鑷姩灏嗗畠杞崲涓烘诞鐐规暟鏍煎紡杩涜鍌ㄥ瓨鐨勩
绛旓細MYSQL锛宒atetime绫诲瀷闀垮害濉8浣嶃傛棩鏈熷瀷锛圖ATETIME锛夋暟鎹槸琛ㄧず鏃ユ湡鏁版嵁锛岀敤瀛楁瘝D琛ㄧず銆傛棩鏈熺殑榛樿鏍煎紡鏄經mm锛廳d锛弝yyy锝濓紝鍏朵腑mm琛ㄧず鏈堝垎锛宒d琛ㄧず鏃ユ湡锛寉yyy琛ㄧず骞村害锛屽浐瀹氶暱搴︿负8浣嶃傛棩鏈熻寖鍥翠负浠嬩簬 100 鍒 9999 骞鐨勬棩鏈鎴栨椂闂存暟鍊笺
绛旓細濡傛灉鏈鍚庢槸锛+08:00 灏辨槸涓8鍖恒傝鏇存敼鏃跺尯锛屽彲浠ョ敤锛欰LTER session SET TIME_ZONE = '+08:00';2.瑕佸皢GMT鏀规垚鎴戜滑鐨勬椂闂锛圕ST锛夛紝鍙互鐢細SELECT to_char(new_time(sysdate, 'GMT', 'CST'), 'MM/DD/YY HH:MI AM') FROM dual锛3.TO_CHAR鍙互璁剧疆鏃ユ湡鏍煎紡锛宎lter session 涔熷彲浠ワ細濡...
绛旓細璁稿瀹㈡埛绔簲鐢ㄧ▼搴忥紙渚嬪閭ｄ簺鍩轰簬 OLE 鑷姩鍖栧璞＄殑瀹㈡埛绔簲鐢ㄧ▼搴忥級閮戒娇鐢 2030 浣滀负鎴骞翠唤銆係QL Server 鎻愪緵涓涓厤缃夐」锛"涓や綅鏁板瓧鐨勬埅姝㈠勾浠"锛夛紝鍊熶互鏇存敼 SQL Server 鎵浣跨敤鐨勬埅姝㈠勾浠藉苟瀵鏃ユ湡杩涜涓鑷存у鐞嗐傜劧鑰屾渶瀹夊叏鐨勫姙娉曟槸鎸囧畾鍥涗綅鏁板瓧骞翠唤銆傚綋浠 smalldatetime 杞崲涓哄瓧绗鏁版嵁鏃讹紝鍖呭惈绉掓垨...
绛旓細2銆佹煡璇細declare @date varchar(20)set @date = '2013-06-06 13:30:25' +'%'SELECT * FROM TELEPHONE_HISTORY WHERE CONVERT(VARCHAR(20) ,RECVTIME, 120)LIKE @date 鍏朵腑锛欳ONVERT(VARCHAR(20) ,RECVTIME, 120) 灏辨槸灏鏃ユ湡 绫诲瀷杞 鈥淵YYY-MM-DD HH:MM:SS鈥 鏍煎紡鐨勫瓧绗︾被鍨嬨
绛旓細涓ょ鏂规硶:1.鏀瑰瓧娈电被鍨,鐢卞師鏉ョ殑datetime鍨嬫敼涓簊malldatetime鍨 2.浣跨敤convert鍑芥暟,鏍煎紡涓篶onvert(鏁版嵁绫诲瀷,瀛楁,鍙傛暟)鍗砪onvert(varchar(10),瀛楁,120)
