string型から日付け型への変換に関して

お世話になります。SQL初心者です。

カラム　'entry_date'に　string型で、
20120506072124123　といったような文字列が入っているとします。

これをdate型に変換したいです。

下記を中心に、CASTを使いながら、変換を色々と試したのですが、エラーがでてしまいます。
非常に基本的な質問で恐縮ですが、よろしくお願いいたします。

select CAST(entry_date AS datetime)
from
テーブル名

退会済みユーザー

2018/09/03 08:48

データベースの名前はなんですか？　質問にデータベース名のタグを付けると回答がつきやすいです。

tynko

2018/09/03 09:08

失礼しました。treasuredataになります

x_x

2018/09/03 09:19

date型ですか？ datetimeですか？

行動規範の内容に同意します

回答6件

ベストアンサー

SQL
1SELECT to_date(CONCAT(SUBSTRING(entry_date, 1, 4) , '-' 
2	  				 , SUBSTRING(entry_date, 5, 2) , '-' 
3					 , SUBSTRING(entry_date, 7, 2) , ' ' 
4					 , SUBSTRING(entry_date, 9, 2) , ':' 
5					 , SUBSTRING(entry_date, 11, 2)  , ':' 
6					 , SUBSTRING(entry_date, 13, 2) , '.' 
7					 , SUBSTRING(entry_date, 15, 3)),'yyyy-mm-dd hh24:mi:ss.SSS')
8
9FROM (テーブル名)
10

DBによってSQLの書き方も違います。(同じ部分もあれば違う部分もある)
ということでDBがTreasureData ということで。

参考URLはこちらとこちら

投稿2018/09/03 09:51

編集2018/09/03 12:01

退会済みユーザー

総合スコア0

tynko

2018/09/03 10:18

誠にありがとうございます。なぜか、 '+' cannot be applied to varchar, varchar(1) というエラーがでてきてしまいます。もし簡単に、エラー原因がわかるようでしたらご教示いただければと思います。お手数おかけし申し訳ございません。

退会済みユーザー

2018/09/03 10:25

すいません。文字列連結で+が使えないのですね。書き直しました。

tynko

2018/09/03 10:28

ありがとうございます。無知で申し訳ありません。実施したのですが、なぜかエラーが出てしまっています。。。Unexpected parameters (varchar, varchar(23)) for function td_time_format. Expected: td_time_format(bigint, varchar, varchar) , td_time_format(bigint, varchar) 　ご存知でしたらご教示いただけますでしょうか。

sazi

2018/09/03 10:33

TD_TIME_FORMAT()はtimestamp型を文字に変換する関数です

退会済みユーザー

2018/09/03 10:34

たびたび申し訳ない。リンク先の英語ドキュメントきちんと読んでいなかった。 CONCAT関数のみですね＞TreasureDataでの文字列連結再び書き直しました。申し訳ない。m(__)m

tynko

2018/09/03 10:41

ご丁寧に本当にありがとうございます。結果が相変わらず、文字列として出てしまいます。結果： 2018/08/27 23:59:25.937yyyy/MM/dd HH:mm:ss.SSS さらにcastをかけるということがよいのでしょうか？

退会済みユーザー

2018/09/03 10:54 編集

>saziさまありがとうございます。ということは18バイト文字列を日付時間文字列と認識するように、 TD_TIME_FORMATしてあげて、そこからCAST関数で変更すればいいってことでしょうか？

退会済みユーザー

2018/09/03 10:49

>toyankさま申し訳ありません。CASTお願いします。

sazi

2018/09/03 10:57

日付型に変換するんですから、TD_TIME_FORMAT()は使いません。タイムスタンプ型の書式に整形してからunix_timestamp()等で日付型に変換するんじゃないかな

tynko

2018/09/03 11:10 編集

やはり、castしてもエラーになってしまいました。 283cks様、sazi様、皆様に、お手数おかけし恐縮です。 extraneous input ''yyyy/MM/dd HH:mm:ss.SSS'' expecting {'.', ')', ',', '[', 'AND', 'AT', 'BETWEEN', 'FILTER', 'IN', 'IS', 'LIKE', 'NOT', 'OR', 'OVER', '=', NEQ, '<', '<=', '>', '>=', '+', '-', '*', '/', '%', '||'}

tynko

2018/09/03 11:11 編集

↑ 283cks様のクエリの最新のエラー内容になります。

退会済みユーザー

2018/09/03 11:33

>toyankさま最初にきちんと聞いておくべきことなのですが・・・＞string型で、20120506072124123 これってJSTにおいての2012年5月6日7時21分24.123秒のことでいいんですよね。（一応string の文字数からそう理解していますが・・・）

tynko

2018/09/03 11:36

>283cks様はい。ご認識の通りでございます。

退会済みユーザー

2018/09/03 11:46

編集しました。確認願います。＞ sazi さまありがとうございました。きちんとドキュメント読んでいませんでした。

tynko

2018/09/03 11:52

283cksさま度々のエラーにも関わらず、本当にありがとうございます。大変恐縮なのですが、 Failed to tokenize string [M] at offset [5]　というエラーになってしまいます。お手数おかけし恐縮です。。。

退会済みユーザー

2018/09/03 12:36 編集

>toyank さま多分 to_date(string, format) のフォーマット書式が違うので、出たエラーかと思います。修正しました。ただミリ秒に関する書式が記載されていません。ので、これでもエラーが出たら、「SUBSTRING(entry_date, 13, 2)」でCONCATを終えて、formatの「.SSS」もはぶいてください。もしかすると to_dateにするときのフォーマットでは無い？のかも。

tynko

2018/09/03 12:33

"これでもエラーが出たら、「SUBSTRING(entry_date, 13, 2)」でCONCATを終えて、formatの「.SSS」もはぶいてください。もしかすると to_charにするときのフォーマットでは無い？のかも。" で無事に変換されました！ただ、型が？となってしまいました。しょうがないんですかね。一度これで出力して、なにか関数で、変換するのがいいのですかね？

退会済みユーザー

2018/09/03 12:36

ごめんなさい。to_charじゃなくてto_date

tynko

2018/09/04 00:56

>283cksさま　本当に色々とありがとうございました。大変助かりました。なんと御礼していいことかわかりませんが、、、心より御礼申し上げます。ありがとうございました。

行動規範の内容に同意します

UNIX_TIMESTAMP()で書式を指定して変換します。

SQL
1SELECT UNIX_TIMESTAMP(entry_date, 'yyyyMMddHHmmssSSS') - 32400

以下参考
TreasureData上で日付と時刻が別々のカラムにあるときのクエリ

投稿2018/09/04 01:11

sazi

総合スコア25354

フォーマットが決まっているなら頑張って文字列を組み立てればいいでしょう.

SQL
1SELECT CONVERT(datetime, CONCAT(SUBSTRING(entry_date, 1, 8), ' ', SUBSTRING(entry_date, 9, 2), ':', SUBSTRING(entry_date, 11, 2), ':', SUBSTRING(entry_date, 13,2), '.', SUBSTRING(entry_date, 15, 3)))

投稿2018/09/03 09:29

x_x

総合スコア13749

tynko

2018/09/03 09:38

ありがとうございます。私の使っているtresuredataはprestoの処理をしているからか、 Function convert not registered　というエラーが出てしまいます。お手数おかけいたしますが、エラー要因がわかりましたら、ご教示いただければと思います。

x_x

2018/09/03 09:39

SQL Server ではないのでしょうか？

tynko

2018/09/03 09:47

大変失礼しました。私の理解不足で、treasuredataはsql serverと同じかと思っておりました。混乱を招いてしまい、本当に申し訳ありません

行動規範の内容に同意します

SQL
1SELECT CAST(SUBSTRING(entry_date, 1, 4) + '/' 
2          + SUBSTRING(entry_date, 5, 2) + '/' 
3          + SUBSTRING(entry_date, 7, 2) + ' ' 
4          + SUBSTRING(entry_date, 9, 2) + ':' 
5          + SUBSTRING(entry_date, 11, 2 )  + ':' 
6          + SUBSTRING(entry_date, 13, 2) + '.' 
7          + SUBSTRING(entry_date, 15, 3) AS DATETIME) 
8
9FROM (テーブル名)
10