忍者ブログ
  ちゃんとカテゴリ分けされておりませんので、 記事をお探しならブログ内検索が便利です。 ご活用くださいませー+.(≧∀≦)゚+.゚
Admin*Write*Comment
[711]  [710]  [709]  [708]  [707]  [706]  [705]  [704]  [703]  [702]  [701

データベース上で日付の計算を行いたいことはよくあります。
集計、はビッグデータ時代には必須ともいえるかもしれません。
MySQLは多くの日付計算のための仕組みを用意してくれています。
遠慮なく使わせていただきましょう^^

ですが、注意点もあります。
それはMySQLはCAST(型変換)がとても強く働くことです。
これは便利なようですが、よく理解しないまま使用すると想定しない値を返却する場合があります。
加えて、関数にたくさんのシノニム(別名:alias)が存在するので、結構現場に混乱をもたらします。
とはいえ便利ですし、レコードの解析の際はお世話になることが多いですので、覚えておいて損はありません。
大事なのは、日付の場合はこの関数。
時間の場合はこの関数。
など決めを作り、運用するのがいいかもしれませんね。
もっとも私は関数あまり使いませんけど~ (えっ

-- 今日
SELECT NOW(), CURDATE(), CURTIME();
-- 日付計算
SELECT
    NOW()
    , NOW() + INTERVAL 1 DAY
    , NOW() + INTERVAL 1 MICROSECOND
    , NOW() + INTERVAL 1 SECOND
    , NOW() + INTERVAL 1 MINUTE
    , NOW() + INTERVAL 1 HOUR
    , NOW() + INTERVAL 1 DAY
    , NOW() + INTERVAL 1 WEEK
    , NOW() + INTERVAL 1 MONTH
    , NOW() + INTERVAL 1 QUARTER
    , NOW() + INTERVAL 1 YEAR
    , NOW() + INTERVAL 1.111111 SECOND_MICROSECOND
    , NOW() + INTERVAL '01:01.111111' MINUTE_MICROSECOND
    , NOW() + INTERVAL '01:01' MINUTE_SECOND
    , NOW() + INTERVAL '01:01:01.111111' HOUR_MICROSECOND
    , NOW() + INTERVAL '01:01:01' HOUR_SECOND
    , NOW() + INTERVAL '01:01' HOUR_MINUTE
    , NOW() + INTERVAL '1 01:01:01.111111' DAY_MICROSECOND
    , NOW() + INTERVAL '1 01:01:01' DAY_SECOND
    , NOW() + INTERVAL '1 01:01' DAY_MINUTE
    , NOW() + INTERVAL '1 01' DAY_HOUR
    , NOW() + INTERVAL '1 1' YEAR_MONTH
\G

中にはMICROSECOND型で返ってくるものもあります。
当然これはこれで正ですが、見慣れた形式にしたい場合はCASTが覚えやすいと思います。

SELECT
    CAST(NOW() AS DATETIME)
    , CAST(NOW() AS DATE)
    , CAST(NOW() AS TIME)
\G

さらに細かく日付の形式を指定するならフォーマットを使います。

SELECT
    NOW()
    , DATE_FORMAT(NOW(), '%a') AS 'Abbreviated weekday name (Sun..Sat)'
    , DATE_FORMAT(NOW(), '%b') AS 'Abbreviated month name (Jan..Dec)'
    , DATE_FORMAT(NOW(), '%c') AS 'Month, numeric (0..12)'
    , DATE_FORMAT(NOW(), '%D') AS 'Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)'
    , DATE_FORMAT(NOW(), '%d') AS 'Day of the month, numeric (00..31)'
    , DATE_FORMAT(NOW(), '%e') AS 'Day of the month, numeric (0..31)'
    , DATE_FORMAT(NOW(), '%f') AS 'Microseconds (000000..999999)'
    , DATE_FORMAT(NOW(), '%H') AS 'Hour (00..23)'
    , DATE_FORMAT(NOW(), '%h') AS 'Hour (01..12)'
    , DATE_FORMAT(NOW(), '%I') AS 'Hour (01..12)'
    , DATE_FORMAT(NOW(), '%i') AS 'Minutes, numeric (00..59)'
    , DATE_FORMAT(NOW(), '%j') AS 'Day of year (001..366)'
    , DATE_FORMAT(NOW(), '%k') AS 'Hour (0..23)'
    , DATE_FORMAT(NOW(), '%l') AS 'Hour (1..12)'
    , DATE_FORMAT(NOW(), '%M') AS 'Month name (January..December)'
    , DATE_FORMAT(NOW(), '%m') AS 'Month, numeric (00..12)'
    , DATE_FORMAT(NOW(), '%p') AS 'AM or PM'
    , DATE_FORMAT(NOW(), '%r') AS 'Time, 12-hour (hh:mm:ss followed by AM or PM)'
    , DATE_FORMAT(NOW(), '%S') AS 'Seconds (00..59)'
    , DATE_FORMAT(NOW(), '%s') AS 'Seconds (00..59)'
    , DATE_FORMAT(NOW(), '%T') AS 'Time, 24-hour (hh:mm:ss)'
    , DATE_FORMAT(NOW(), '%U') AS 'Week (00..53), where Sunday is the first day of the week; WEEK() mode 0'
    , DATE_FORMAT(NOW(), '%u') AS 'Week (00..53), where Monday is the first day of the week; WEEK() mode 1'
    , DATE_FORMAT(NOW(), '%V') AS 'Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X'
    , DATE_FORMAT(NOW(), '%v') AS 'Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x'
    , DATE_FORMAT(NOW(), '%W') AS 'Weekday name (Sunday..Saturday)'
    , DATE_FORMAT(NOW(), '%w') AS 'Day of the week (0=Sunday..6=Saturday)'
    , DATE_FORMAT(NOW(), '%X') AS 'Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V'
    , DATE_FORMAT(NOW(), '%x') AS 'Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v'
    , DATE_FORMAT(NOW(), '%Y') AS 'Year, numeric, four digits'
    , DATE_FORMAT(NOW(), '%y') AS 'Year, numeric (two digits)'
    , DATE_FORMAT(NOW(), '%%') AS 'A literal “%” character'
    , DATE_FORMAT(NOW(), '%x') AS 'x, for any “x” not listed above'
\G


時刻形式からの展開

SELECT
    NOW()
    , EXTRACT(DAY FROM NOW())
    , EXTRACT(MICROSECOND FROM NOW())
    , EXTRACT(SECOND FROM NOW())
    , EXTRACT(MINUTE FROM NOW())
    , EXTRACT(HOUR FROM NOW())
    , EXTRACT(DAY FROM NOW())
    , EXTRACT(WEEK FROM NOW())
    , EXTRACT(MONTH FROM NOW())
    , EXTRACT(QUARTER FROM NOW())
    , EXTRACT(YEAR FROM NOW())
    , EXTRACT(SECOND_MICROSECOND FROM NOW())
    , EXTRACT(MINUTE_MICROSECOND FROM NOW())
    , EXTRACT(MINUTE_SECOND FROM NOW())
    , EXTRACT(HOUR_MICROSECOND FROM NOW())
    , EXTRACT(HOUR_SECOND FROM NOW())
    , EXTRACT(HOUR_MINUTE FROM NOW())
    , EXTRACT(DAY_MICROSECOND FROM NOW())
    , EXTRACT(DAY_SECOND FROM NOW())
    , EXTRACT(DAY_MINUTE FROM NOW())
    , EXTRACT(DAY_HOUR FROM NOW())
    , EXTRACT(YEAR_MONTH FROM NOW())
\G


UNIX TIMESTAMP

SELECT
    NOW()
    , UNIX_TIMESTAMP()
    , FROM_UNIXTIME(UNIX_TIMESTAMP())
    , FROM_UNIXTIME(UNIX_TIMESTAMP()) + 0
    , FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x')
\G

月末計算
日付を含む型であること。
有効は日付形式であること

SELECT
    NOW()
    , LAST_DAY(NOW())
    , LAST_DAY(CURDATE())
    ;


時刻、日付作成
MAKETIME()は0パディングされたフォーマットは受け付けない。

SELECT
    NOW()
    , MAKEDATE(EXTRACT(YEAR FROM NOW()), 1)
    , MAKEDATE(EXTRACT(YEAR FROM NOW()), 141)
    , MAKETIME(EXTRACT(HOUR FROM NOW())+0, EXTRACT(MINUTE FROM NOW())+0, EXTRACT(SECOND FROM NOW())+0)
    , MAKETIME(EXTRACT(HOUR FROM NOW())+1, EXTRACT(MINUTE FROM NOW())+1, EXTRACT(SECOND FROM NOW())+1)
\G


いわゆるstrptime()

-- Apacheのログから拾ってみたけど、MySQLはTimezoneのフォーマットがないのね(^_^;)
SELECT STR_TO_DATE('[16/Jan/2015:10:39:13 +0900]','[%d/%b/%Y:%T +0900]');


weekday
(0 = Monday, 1 = Tuesday, … 6 = Sunday).


SELECT
    NOW()
    , WEEKDAY(NOW() + INTERVAL 0 DAY)
    , WEEKDAY(NOW() + INTERVAL 1 DAY)
    , WEEKDAY(NOW() + INTERVAL 2 DAY)
    , WEEKDAY(NOW() + INTERVAL 3 DAY)
    , WEEKDAY(NOW() + INTERVAL 4 DAY)
    , WEEKDAY(NOW() + INTERVAL 5 DAY)
    , WEEKDAY(NOW() + INTERVAL 6 DAY)
\G


その他時刻差分とか出せるみたいだけど、こちらはUNIX TIMESTAMPを中継して
整数で計算した方が良いと思うの。
わかりやすいしね。
マイクロ秒まで考慮する必要があるならいいけど、MySQLのカラム型にマイクロ秒まで
考慮できるデータがあったっけ?
という感じです (^-^;)

でわでわ

拍手

PR



COMMENT
Name
Title
Color
Mail
URL
Text
Pass   Vodafone絵文字 i-mode絵文字 Ezweb絵文字
secret
ブログ内検索
カレンダー
04 2017/05 06
S M T W T F S
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 30 31
カウンター
最新コメント
最新トラックバック
プロフィール
+ハンドル+
y_ayamori(purple)
+職業+
IT系エンジニア
+すまい+
さいたま
バーコード
ブログパーツ
アバター
Copyright © アナログを愛するデジタル生活館 All Rights Reserved.
photo by Kun material by Atelier Black/White Template by Kaie
忍者ブログ [PR]