{"id":4143,"date":"2020-10-15T10:53:28","date_gmt":"2020-10-15T02:53:28","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=4143"},"modified":"2024-06-24T15:46:12","modified_gmt":"2024-06-24T07:46:12","slug":"inceptor%e5%a6%82%e4%bd%95%e8%ae%a1%e7%ae%97%e6%97%b6%e9%97%b4%e5%b7%ae%ef%bc%88ms-sec-min-hour-day-week-month-year%ef%bc%89","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=4143","title":{"rendered":"inceptor\u5982\u4f55\u8ba1\u7b97\u65f6\u95f4\u5dee"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p><strong>inceptor\u5982\u4f55\u901a\u8fc7<code>timestampdiff<\/code>\u51fd\u6570\u7b49\u65b9\u5f0f\uff0c\u8ba1\u7b97\u5404\u79cd\u65f6\u95f4\u5355\u4f4d\u7684\u65f6\u95f4\u5dee<\/strong><\/p>\n<p>\u8fd9\u91cc\u4ecb\u7ecd\u4e0b<code>timestampdiff<\/code>\u51fd\u6570\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th>\u51fd\u6570\u7528\u6cd5<\/th>\n<th>\u8fd4\u56de\u7c7b\u578b<\/th>\n<th>\u63cf\u8ff0<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>timestampdiff(unit, start_timestamp, end_timestamp)<\/td>\n<td>INT<\/td>\n<td>\u8ba1\u7b97\u4e24\u4e2a\u65e5\u671f\/\u65f6\u95f4\u4e4b\u95f4\u7684\u65f6\u95f4\u5dee\uff0c\u5e76\u4ee5\u6307\u5b9a\u7684\u65f6\u95f4\u5355\u4f4d\u8fd4\u56de\u7ed3\u679c\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<blockquote>\n<p>\u53c2\u6570\u8bf4\u660e\uff1a<br \/>\nunit\uff1a\u8868\u793a\u65f6\u95f4\u5dee\u7684\u5355\u4f4d\uff0c\u53d6\u503c\uff1ayear\u3001quarter\u3001month\u3001week\u3001day\u3001hour\u3001minute\u3001second\u3002<br \/>\nstart_timestamp\uff1a\u8868\u793a\u5f00\u59cb\u65f6\u95f4\u7684\u65e5\u671f\u6216\u65f6\u95f4\u6233\u3002<br \/>\nend_timestamp\uff1a\u8868\u793a\u7ed3\u675f\u65f6\u95f4\u7684\u65e5\u671f\u6216\u65f6\u95f4\u6233\u3002<\/p>\n<\/blockquote>\n<p>\u6ce8\u610f\uff1a\u4f4e\u7248\u672cinceptor\u662f\u4e0d\u652f\u6301timestampdiff\u51fd\u6570\u7684\uff0c\u9700\u8981\u4f7f\u7528\u5176\u4ed6\u51fd\u6570\u6539\u5199\u3002<\/p>\n<h3>\u8be6\u7ec6\u8bf4\u660e<\/h3>\n<hr \/>\n<h4>1. \u6beb\u79d2<\/h4>\n<pre><code class=\"language-sql\">SELECT\n    MILLISECOND('2019-01-01 22:10:10.111') - MILLISECOND('2019-01-01 22:10:10.100') +\n    (\n        unix_timestamp(cast('2019-01-01 22:10:10.111' AS TIMESTAMP)) -\n        unix_timestamp(cast('2019-01-01 22:10:10.100' AS TIMESTAMP))\n    )*1000\nFROM system.dual;<\/code><\/pre>\n<h4>2. \u79d2<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('second', '2019-01-01 22:00:10', '2019-01-01 22:10:10') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    unix_timestamp('2019-01-01 22:10:10','yyyy-MM-dd HH:mm:ss') -\n    unix_timestamp('2019-01-01 22:00:10','yyyy-MM-dd HH:mm:ss')\nFROM system.dual;<\/code><\/pre>\n<h4>3. \u5206\u949f<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('minute', '2019-01-03 22:00:10', '2019-01-02 23:10:20') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    (minute('2019-01-02 23:10:20')-minute('2019-01-03 22:00:10')) +\n    (\n        hour('2019-01-02 23:10:20') - hour('2019-01-03 22:00:10') +\n        (\n            datediff('2019-01-02 23:10:20','2019-01-03 22:00:10')\n        )*24\n    )*60\nFROM system.dual;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    (\n    unix_timestamp('2019-01-02 23:10:20','yyyy-MM-dd HH:mm:ss') -\n    unix_timestamp('2019-01-03 22:00:10','yyyy-MM-dd HH:mm:ss')\n    )\/60\nFROM system.dual;<\/code><\/pre>\n<h4>4. \u5c0f\u65f6<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('hour', '2019-01-03 22:00:10', '2019-01-02 23:00:10') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    hour('2019-01-02 23:00:10')-hour('2019-01-03 22:00:10') +\n    (\n        datediff('2019-01-02 23:00:10','2019-01-03 22:00:10')\n    )*24\n    as hour_subValue\nFROM system.dual;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    (\n    unix_timestamp('2019-01-02 23:00:10','yyyy-MM-dd HH:mm:ss') -\n    unix_timestamp('2019-01-03 22:00:10','yyyy-MM-dd HH:mm:ss')\n    )\/3600\nFROM system.dual;<\/code><\/pre>\n<h4>5. \u5929<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('day', '2015-02-08 22:10:10','2015-01-15 22:10:10') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    DATEDIFF('2015-01-15','2015-02-08')\nFROM system.dual;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    (\n    unix_timestamp('2015-01-15 22:10:10','yyyy-MM-dd HH:mm:ss') -\n    unix_timestamp('2015-02-08 22:10:10','yyyy-MM-dd HH:mm:ss')\n    )\/3600\/24\nFROM system.dual;<\/code><\/pre>\n<h4>6. \u661f\u671f<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('week', '2015-01-15','2015-02-08') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    floor(abs(DATEDIFF('2015-01-15','2015-02-08')\/7))\nFROM system.dual;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    (\n    unix_timestamp('2015-01-15','yyyy-MM-dd') -\n    unix_timestamp('2015-02-08','yyyy-MM-dd')\n    )\/3600\/24\/7 \nFROM system.dual;<\/code><\/pre>\n<h4>7. \u6708<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('month', '2010-10-15','2011-01-01') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    MONTHS_BETWEEN('2010-10-15','2011-01-01')\nFROM system.dual;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    EXTRACT(MONTH FROM '2010-10-15') - EXTRACT(MONTH FROM '2011-01-01')+\n    (\n    EXTRACT(YEAR FROM '2010-10-15') - EXTRACT(YEAR FROM '2011-01-01')\n    )*12\nFROM system.dual;<\/code><\/pre>\n<h4>8. \u5e74<\/h4>\n<pre><code class=\"language-sql\">SELECT timestampdiff('year', '2015-01-15','2016-01-15') AS result;<\/code><\/pre>\n<pre><code class=\"language-sql\">SELECT\n    EXTRACT(YEAR FROM '2015-01-15') - EXTRACT(YEAR FROM '2016-01-15') \nFROM system.dual;<\/code><\/pre>\n<pre><code class=\"language-sql\">--\u6ce8\u610f\u4e0d\u8981\u7528\u5927\u5199\u7684YYYY\uff0c\u5728java\u4e0bYYYY\u662fweek-based-year\nSELECT\n    to_char('2026-12-31', 'yyyy') - to_char('2016-12-31', 'yyyy')\nFROM system.dual;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 inceptor\u5982\u4f55\u901a\u8fc7timestampdiff\u51fd\u6570\u7b49\u65b9\u5f0f\uff0c\u8ba1\u7b97\u5404\u79cd\u65f6\u95f4\u5355\u4f4d\u7684\u65f6\u95f4\u5dee \u8fd9\u91cc\u4ecb\u7ecd\u4e0bt ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=4143\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[],"class_list":["post-4143","post","type-post","status-publish","format-standard","hentry","category-configuration"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/4143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4143"}],"version-history":[{"count":5,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/4143\/revisions"}],"predecessor-version":[{"id":13437,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/4143\/revisions\/13437"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}