Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
5
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

【MySQL】date型とtime型に分けられたカラムを結合する方法

【MySQL】date型とtime型に分けられたカラムを結合する方法

構文

DATE_ADD(date,INTERVAL expr unit)

SQL文(サンプル)

SELECT 
date_add(CAST([TRANSACTIONDATE] AS DATETIME), INTERVAL [TRANSACTIONTIME] HOUR_SECOND)
FROM table;

手順(考え方)

1 date型で定義されたカラムをdatetime型に変換する (2018-01-11 -> 2018-01-11 00:00:00)
1 datetime型に変換したカラム値に、time型のカラムの値を加算する (date_add関数)

unit 値ごとに要求される形式の expr 引数

expr = 文字列 unit = 式を解釈する際の単位を示すキーワード

unit値 要求される expr 書式
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
5
Help us understand the problem. What are the problem?