SQLite3のtext→date変換について

質問

質問お願いします。
text型で持っている[日付＋時刻]のデータをdate型に変換後、そのデータと現在時刻の引き算を行いたいのですがSQLiteだとどのような関数を用いれば実現できるのでしょうか？

【状況】
◇現在はtextデータでyyyymmddhh24missを所有。
◇date型に変換できず困惑中。
◇まだ先ですが...引き算をするにはやっぱりInt型に変換が必要？
　もしくは関数でtoSecond(sysdate, 保持date型データ)みたいなことが可能？

よろしくお願い致します。

回答No.1

himajin100000 · ベストアンサー

データベースの勉強から意図的に逃げてたんで詳しくなく手探りです。

なんというかSQLiteの仕様にイラッとしました。

//======================================
http://www.sqlite.org/datatype3.html

>Datatypes In SQLite Version 3
SQLite 3におけるデータ型

(中略)

>1.2 Date and Time Datatype
1.2 日付と時刻のデータ型

>SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

SQLiteには日付や時刻を格納して蓄えておくためのクラスはない。そのかわり、SQLiteのビルトインの日付時刻関数は、日付の時刻をTEXT,REAL,INTEGERの値のどれかとして格納する

>TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
>REAL as Julian day numbers, the number of days since noon in on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
>INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

TEXTはISO8601の文字列
REALはグリニッジ天文台における、先発グレゴリオ暦 紀元前4714年 正午からの経過日数
INTEGERは1970-01-01 00:00:00 UTCからの経過秒数

>Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
アプリケーションはこれらのどのフォーマットで日付や時刻のデータを格納するかというのを選択でき、ビルトインの日付時刻関数を使ってこれらを自由に変換できる。
================================//

さて、ISO8601として認められる書式は

http://www.sqlite.org/lang_datefunc.html

のTime Stringsに乗っている(訳すのは面倒だがわかるだろう)

自分で変換しろってかorz

文字列操作をいちいちやる。
http://ideone.com/5EOaS

ユーザー定義関数みたいなものが作れれば楽なんだけどなあ、確かストアドプロシージャって名前聞いたことあるような。うん、あってるか自信ないけど、とりあえず機能的には、それっぽい
//===========================
http://www.sqlite.org/whentouse.html

>Appropriate Uses For SQLite

SQLiteの適切な利用法

>SQLite is different from most other SQL database engines in that its primary design goal is to be simple:

SQLiteは他の大半のデータベースエンジンと違って、その主な設計目標は「シンプルである」ことである。

(中略)

>Simplicity in a database engine can be either a strength or a weakness, depending on what you are trying to do. In order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth. If you need some of these features and do not mind the added complexity that they bring, then SQLite is probably not the database for you. SQLite is not intended to be an enterprise database engine. It is not designed to compete with Oracle or PostgreSQL.

データベースエンジンにおいて「シンプルさ」は、あなたがやろうとしていること次第で、強みにもなるし弱みにもなる。「シンプルさ」を達成するため、SQLiteは、便利だと感じる人もいる他の機能 - 高い同時実行性やきめ細かいアクセス制御、大量のビルトイン関数、【ストアドプロシージャ】、難解なSQL言語機能 (以下略) 等を犠牲にしなければならなかった。
======================================//
…このまま関数に纏めて単純化は出来ないってことねorz

その日付時刻関数とやらを見てみましょうかね。
http://www.sqlite.org/lang_datefunc.html
ええと、modifierには第一引数に指定された日時に対して操作を加えられる。unixepochは後ろがepochからの秒数を表す数値をDDDDDDDDDD形式にした文字列でない場合には未定義…だから役に立たない。julianday以外は文字列を返しちゃうのでこれしか使えません。julianday関数はさっきも言ったとおり基準日からの日数が帰ります

ってことで、さっきちょっと見せちゃったけど
こういうふうに書くことになった。

http://ideone.com/5EOaS

で、2013年01月12日7時55分1秒と2012年01月12日7時55分1秒の差を求めると、
2012年は閏年なので366.0日が出力されている。だからこれが正しいんだろう。

#と、ここまで書いてから思ったけど、何で3月じゃなくて1月でやったんだろうね、俺

お礼

回答遅くなってしまい申し訳有りませんでした。
大変参考になりました、有り難うございます。

やはりこのようにゴリゴリに組まなければならないのですね。。。
教えて頂けた方法をファンクションにして、実用レベルの高いものが作れればソレを利用する方向で考えて行こうと思います。
というか...その柔軟性がSQLiteの売りなのかな。。とか思いました。

ありがとうございました。