Q&A
データベースの名前はなんですか？ 質問にデータベース名のタグを付けると回答がつきやすいです。
お世話になります。SQL初心者です。
カラム 'entry_date'に string型で、
20120506072124123 といったような文字列が入っているとします。
これをdate型に変換したいです。
下記を中心に、CASTを使いながら、変換を色々と試したのですが、エラーがでてしまいます。
非常に基本的な質問で恐縮ですが、よろしくお願いいたします。
--
select CAST(entry_date AS datetime)
from
テーブル名
--
回答6件
0
ベストアンサー
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 ということで。
投稿2018/09/03 09:51
編集2018/09/03 12:01
総合スコア138
誠にありがとうございます。 なぜか、 '+' cannot be applied to varchar, varchar(1)
というエラーがでてきてしまいます。
もし簡単に、エラー原因がわかるようでしたらご教示いただければと思います。
お手数おかけし申し訳ございません。
すいません。文字列連結で+が使えないのですね。書き直しました。
ありがとうございます。無知で申し訳ありません。実施したのですが、なぜかエラーが出てしまっています。。。Unexpected parameters (varchar, varchar(23)) for function td_time_format. Expected: td_time_format(bigint, varchar, varchar) , td_time_format(bigint, varchar) ご存知でしたらご教示いただけますでしょうか。
TD_TIME_FORMAT()はtimestamp型を文字に変換する関数です
たびたび申し訳ない。リンク先の英語ドキュメントきちんと読んでいなかった。
CONCAT関数のみですね＞TreasureDataでの文字列連結
再び書き直しました。申し訳ない。m(__)m
ご丁寧に本当にありがとうございます。
結果が相変わらず、文字列として出てしまいます。
結果：
2018/08/27 23:59:25.937yyyy/MM/dd HH:mm:ss.SSS
さらにcastをかけるということがよいのでしょうか？
>saziさま
ありがとうございます。
ということは18バイト文字列を日付時間文字列と認識するように、
TD_TIME_FORMATしてあげて、そこからCAST関数で
変更すればいいってことでしょうか？
>toyankさま
申し訳ありません。CASTお願いします。
日付型に変換するんですから、TD_TIME_FORMAT()は使いません。
タイムスタンプ型の書式に整形してからunix_timestamp()等で日付型に変換するんじゃないかな
やはり、castしてもエラーになってしまいました。 283cks様、sazi様、皆様に、お手数おかけし恐縮です。
extraneous input ''yyyy/MM/dd HH:mm:ss.SSS'' expecting {'.', ')', ',', '[', 'AND', 'AT', 'BETWEEN', 'FILTER', 'IN', 'IS', 'LIKE', 'NOT', 'OR', 'OVER', '=', NEQ, '<', '<=', '>', '>=', '+', '-', '*', '/', '%', '||'}
↑ 283cks様のクエリの最新のエラー内容になります。
>toyankさま
最初にきちんと聞いておくべきことなのですが・・・
＞string型で、20120506072124123
これってJSTにおいての2012年5月6日7時21分24.123秒のことでいいんですよね。
（一応string の文字数からそう理解していますが・・・）
>283cks様
はい。ご認識の通りでございます。
編集しました。確認願います。
＞ sazi さま
ありがとうございました。きちんとドキュメント読んでいませんでした。
283cksさま
度々のエラーにも関わらず、本当にありがとうございます。大変恐縮なのですが、
Failed to tokenize string [M] at offset [5]
というエラーになってしまいます。お手数おかけし恐縮です。。。
>toyank さま
多分 to_date(string, format) のフォーマット書式が違うので、出たエラーかと
思います。修正しました。ただミリ秒に関する書式が記載されていません。
ので、これでもエラーが出たら、「SUBSTRING(entry_date, 13, 2)」でCONCATを
終えて、formatの「.SSS」もはぶいてください。
もしかすると to_dateにするときのフォーマットでは無い？のかも。
"これでもエラーが出たら、「SUBSTRING(entry_date, 13, 2)」でCONCATを
終えて、formatの「.SSS」もはぶいてください。
もしかすると to_charにするときのフォーマットでは無い？のかも。" で無事に変換されました！
ただ、型が？となってしまいました。しょうがないんですかね。一度これで出力して、なにか関数で、変換するのがいいのですかね？
ごめんなさい。to_charじゃなくてto_date
>283cksさま 本当に色々とありがとうございました。大変助かりました。なんと御礼していいことかわかりませんが、、、心より御礼申し上げます。ありがとうございました。
0
UNIX_TIMESTAMP()で書式を指定して変換します。
SQL
1SELECT UNIX_TIMESTAMP(entry_date, 'yyyyMMddHHmmssSSS') - 32400
投稿2018/09/04 01:11
総合スコア25195
0
フォーマットが決まっているなら頑張って文字列を組み立てればいいでしょう.
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
総合スコア13749
0
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
まあ文字列17バイトをデータベースは日付型とは認識していないのでね。
投稿2018/09/03 09:08
総合スコア138
ありがとうございます。
私の、使用しているDBが異なるからか、
Unknown type: datetime と出てしまいます。お手数おかけしますが、エラー要因わかりましたらご教示いただければと思います。
※prestoという処理を使っている、DBはtreasuredataを使っております。
まず、SQL Serverタグを外せるなら外しましょう。
大変失礼しました。タグから外しました。混乱を招いてしまい、本当に申し訳ありません。
いえいえ。回答書き直しました。よろしくお願いします。
0
検証してないのですが、SELECT dateadd(s,CAST(entry_date as bigint),'19700101 05:00:00:000');な感じはどうでしょう。
entry_dateを数値の羅列ってだけでuinx秒だと思ってしまいました。取り下げます(相応しい回答が他にあるため、取り下げのみ)
投稿2018/09/03 08:52
編集2018/09/03 09:10
総合スコア12705
早速ありがとうございます。
エラーとして、
Column 's' cannot be resolved が出てしまいます。sはなにかを代入するということになりますでしょうか？
代入ではないです。SQL Serverですよね？
参考にしたのは、
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017
なのですが(secondとかで試してみるとあるいは)
すみません。勘違いしてました。これunix秒じゃないのですね、忘れてください。
0
そのまま型変換してもダメなので、
年月日時分秒それぞれの数字を部分的に切り出す関数と、
'-' や ' ' などの文字列との連結で
2018-09-03 17:49:00
みたいな文字列に仕立てればCASTできるはずです。
投稿2018/09/03 08:50
退会済みユーザー
総合スコア0
早速ありがとうございます。おっしゃっていただいたことが、まだ理解できていないのですが、もう少しヒントいただけないでしょうか。無知で恐縮ですが、ご教示いただけますと幸いです。
あなたの回答
tips
プレビュー
