Q&A
SQLiteのことでしたらタグに追加されては？また「年だけしかわからない」という状況がわかりません。
データは設計のうちなので自由入力のものを除いて大抵は型や桁が決まっていて入ってくるデータも決まっているはずです。
趣味でSQLiteの勉強をしています。ふわっとした質問になります…。
SQLiteにはdatetimeはないみたいですが、 yyyy-mm-dd のかたちで日付のデータを格納してみました。
ところで、レコードによっては年だけしかわからないみたいなのもありました。
そういうレコードが混ざるようなとき、 yyyy-NA-NA みたいなかたちでデータを格納することは可能でしょうか？
一般的にはどういう風にやるものですか？
◆考えたこと1
年月日でそれぞれカラムをわける。
year、month、dayのINTEGER型のカラムをつくって、レコードによってはmonthとかdayとかだけNULLになったりする。
◆考えたこと2
カラムはひとつにまとめて、不明なやつはゼロとかにしておく。
年だけわかるなら 2018-00-00 とかにする。
1でも2でも、どちらのやりかたでもレコードを日付順に並べたり、特定の期間だけをSELECTしたりはできそう。
1だと自力で日付を生成しないといけないの面倒そう…。
2だと00日とか存在しないので関数とかで予期しないエラーが起きたりするかも…？
わたしが使う場合、SQLからデータを呼ぶほかに、PythonでSQLを操作することを考えているので、
Python側できれいに解決することを前提にデータベースを作るのでもいいと思っています。
ときどきWebサービスとかでこういうのがあると思いますが
（ブクログがそうかと思って確認したんですが勘違いでした…どこだったんだろう…）、
一般的にこういう場合には内部でどのようにデータを持っているのでしょうか？
具体的に何ですか？年月日が決まるべきカラムなのに年しかわからないということですよね？
mts10806さん、papinianusさん、ありがとうございます。
ブクログのように読書の履歴をSQLiteで管理しているとします。
id：通し番号：INTEGER型
title：タイトル：TEXT型
writer：著者：TEXT型
date：読了日：日付…？
といったカラムを用意して、dateに読了日を格納します。
しかし、入力者するひとがまめではないので、いつ読了したのかはっきりしないことがあるとします。
・本A：2018年12月15日に読んだことがわかっている
・本B：2018年12月に読んだ本だがその月の何日かはわからない
・本C：2018年に読んだのは間違いないが詳しい記憶は曖昧
という3冊の本の情報をデータベースに入れたいです。
曖昧な情報が混ざるレコードも含めて、
・2018年12月に読んだ本をSELECT→AとBが出てくる
・2018年に読んだ本をSELECT→AとBとCが出てくる
といったことができるようにしたいのですが可能でしょうか？ということを伺いたいです！
その手のサービスは使ったことないのですが、そのブクログにおいて、2018年のみという入力は可能ですか？可能でないとしたら質問者様はそういうときにどう入力していますか？
papinianusさま
実際にはブクログにその機能はありませんでした…。
なにか別のサイトと勘違いしているような気がするのですが、思い出せませんでした…。すみません。
https://i2.wp.com/www.ex-it-blog.com/wp-content/uploads/ex-it_81.png
わたしのイメージとしては上記画像に近いです。
年月日それぞれ分かれたメニューがあり、
「日」の中には「1」「2」…「31」と選択肢が並ぶ最後に「年を指定しない」がある感じです。
年月も日と同じです。
回答5件
0
日付の有効フラグを別途カラムを設置し、日付には1月1日を入れておけば
よいのでは？
| 日付 | 有効 | 備考 |
|---|---|---|
| 2019-01-24 | 0 | 日付として有効 |
| 2019-01-01 | 1 | 年月として有効 |
| 2019-01-01 | 2 | 年として有効 |
実際に年月や年として有効の場合日付はその範囲ないであればいつでも
いいのですが、存在しない可能性を考えると各月の1日や1月1日を
指定するのが確実です
投稿2019/01/24 02:36
総合スコア114784
ありがとうございます。
2018年で、月日がわからない場合、`2018-01-01` など実際に存在する日付を指定してしまうと、
本当に1月1日を指定するのと区別がつかなくなってしまう…と悩んでいたんですが、
有効かどうかを別のフラグで保持すればいいのですね。
その場合、SELECTでざっくり2018年のデータを探す、などのときには
細かいことを気にせず日付のカラムだけを見ればいいし、
詳細が不明かどうかをきちんと把握する必要があるときには
有効かどうかのフラグのカラムを確認すればいいということで、なんだかよさそう。
0
ベストアンサー
yambejp様の回答がファイナルアンサーなんですが、弊害なども検討したいみたいなので。
- 考えかた
質問者様自身がユーザであるときに、どう入力しているか、です。仮にコメントで添付された画像のようなインプット欄縛りだったときに、じゃあ質問者様自身が「去年読んだんだよね～」っていうときにどう入れるかです。まあおおみそかとか正月とか入れるんじゃないですかね。それをDBに入れればいいと思います。
- 1の問題点
数値にわけたとき、31までしか入らないint型っていうのはあまり世間に流通してないので、32をエラーにするのがつらい。また3つのセルをあわせたときに2019/2/29や2018/4/31をエラーにするのがつらいです。
アプリで頑張るのはもちろんなんですが、日付が妥当であることをDBのカラムの型が保証できない、のはつらいです。
- 2の利点
2の問題点の解決は、どこまで有効かをフラグでもつ解決がでてますのでそれとして、これが何がうれしいかって言うと、範囲検索がきくんですよね。去年読んだ本、先月読んだ本というのが、検索できる。
そして、年しか指定しなかったものが、ソートしたときに、去年読んだ本の中で最初に来て欲しいなら1/1にすればいいし、年しか入ってないものは最後に来てほしいなら12/31を指定すればいい(月も同じ)
逆にそういうことをしなくていい、日付なんていうのは参照目的のものでしかなくて、それでソートしたり検索したりするニーズが全くないなら、フラグ管理のほうがわずらわしいので、考え方1にするか、みたいな判断もあり得ると思います。
(そういう意味では、極論、用途次第ってことにもなりますが)
投稿2019/01/24 05:38
総合スコア12705
> 2019/2/29や2018/4/31をエラーにするのがつらいです。
たしかに大変やりづらそう…。
Pythonを使って弾くことはやればできるかもしれませんが、SQLで完結するほうが好ましいですね。
> そういう意味では、極論、用途次第ってことにもなります
用途次第というのはその通りかもな、というのは質問を立てた時点でなんとなく思ってはいました。
単に日付を記録したいだけで、今後一生ソートも検索もしないなら、全部文字列でデータを持っていればいいですよね。
ただ、曖昧なデータは曖昧なりに、そうでないはっきりしたデータははっきりしたなりに
ソートや検索をして活用したい、混在したデータの範囲内でできるだけ活用したい、
と思ったときに、どういうデータの持ち方がいいのかをうかがいたく質問しました。
また、その場合「活用」をどう定義したらエンジニアさんの思考をトレースできるのか、
という点にも興味があり質問しました。
だいぶクリアになったように思います。ありがとうございます。
0
一般的かどうかは知りませんが、私は２ですね。但し数値型です。
なぜそうしているかを経緯を交えて説明します。
最初の頃は私は、日付は日付型として管理する方が日付型に関する色々な関数が直接使えるし効率が良いと考えていました。
ただ、期間を指定するような場合に、開始～終了に無期限のような設定は行えず9999/12/31とか、別に無期限用のフラグを持たせるというのはなんとなくしっくりこなかったのです。
ある時、自治体のデータを扱うことがあって、その時のデータは項目が分かれており、月や日が'不明'として入力されていました。
年・月・日を分けた上でさらに判断が必要になるのは明らかに効率が悪そうです。
困った私は、数値型でyyyymmddという形式でmmやddは00や99を不明などに割り当てるという方法を試してみました。
これが、意外に最強でした。
日付の書式に変換が必要な場面はありましたが、それは画面や帳票やCSVなどに出力する場合に単に書式を設定するような類です。
例えばyyyy/mm/ddの様な書式なのですが、書式というだけで日付型に変換する必要はありません。
数値型に書式を設定するので、0000/00/00というようにするだけです。
逆に、SQLでの結合や抽出の条件するような場合に判定をする必要が無くこれが非常に効率的でした。
開始～終了の様な期間で無制限は0～99999999と設定すればよく、判定が必要な場合も値で可能ですしね。
あ、文字型にするのは数値型より遅くなるのでハナから考えていませんでした。
という訳で、日付を扱うのは数値型最強説でした。
但し、ログのようなデータ（作成日とか更新日とかの記録系）は日付型の方が良いので、制御に使う項目に限定ですけど。
投稿2019/01/25 01:31
編集2019/01/29 14:08
総合スコア25173
ありがとうございます！
https://teratail.com/questions/35740
こちらの質問にも出てくる方法ですね。
期間を指定してSELECTしたりするときには数値の大小を比較するだけなのでカンタンそう。
年は問わず、1月に絞りたいなどの場合はLIKEを使って `____05__` みたいにすればいいですね。
これで困ることがあるとすると、
・実在する日付かどうかを調べるのが難しい
・日付同士の引き算などが難しい
という感じでしょうか。
この場合はどちらもyyyy/mm/ddにしてからPythonとかに渡す感じかと思いました！ なるほど…！
うーん、伝わってない気がします。
あれっ…取り違えましたでしょうか…????
リンク先の回答とは「型に関係なく書式を揃えるだけ」という意味ではマッチしているのはそうなのですが、伝えたいのは、「数値型最強」なので文字型ではないです。
数値なのでlikeは使えません。月だけで絞るというのは商を使用します。
文字型より数値型で扱う事に意義があるのです。
>・実在する日付かどうかを調べるのが難しい
>・日付同士の引き算などが難しい
やってみれば分かるのですけど、入れ子が一つ増えるくらいですよ。
例：日付関数(formatなどの日付書式への変換関数）
> 数値なのでlikeは使えません。月だけで絞るというのは商を使用します。
あっそうか…おっしゃる通りですね。
割り算（小数点以下を切り捨て）で日の部分をなくして、
割り算の余りを使えば年の部分をなくして月の部分だけを残せる！
年を0000にして数値にしたら桁数が揃わないのでは…？
とか思ったんですが、考えてみたら揃ってなくても構わないんですよね。
揃えることにこだわるなら、年が不明の場合に9999にするなどすればいいですね〜。
再度コメントいただきありがとうございます！
0
私なら、1900/01/01 を 1日目として、整数で何日目かを保存します。UNIX時間の日付版のようなものだと考えてください。
年月しかわからない場合はその月の1日、年しかわからない場合はその年の1/1の日付にし、そのデータの確度（有効フラグのようなもの）を別カラムに持つようにします。
「確度」といったのは、例えば年しかわからない場合は取る値の範囲が+0〜+365（+366）なのでその範囲の最大値をいれるかなぁと思ったからです。なぜ取りうる日数の範囲にしたかというと、将来的に、曖昧には覚えてるが、「第何週」とか「季節」とかで覚えてるみたいな需要がある場合にも対応できるからです。
投稿2019/01/29 14:52
総合スコア30
0
あまりよい作りではないですが、年月日をそれぞれ4桁2桁2桁の別個のカラムにして（NULL OK）それぞれ保存する。
投稿2019/01/24 00:01
総合スコア80850
ありがとうございます。
わたしの「◆考えたこと2」と同じ感じかと思います。
考えられる弊害はありますか？
違いますよ。
nullと0は別物です。
私の例ではあくまでカラム自体を
year / month / day
のように別々に持つ提案をしています。
検索の際に年だけ、月だけとあるのでしたら割と有用かと思います。
date型では存在しない日付を持つことはできないので2018-00-00のような持ち方はできません。
弊害というと、そこだけしか入力されない場合NULLのカラムがあるレコードが多くなるということでしょうか。そこまでの弊害には思いませんが。
何を目指しているか明確にしてください。
すみません、◆考えたこと1、でした…！
> 何を目指しているか明確にしてください。
あいまいですね…すみません。
判断するのにあたっては、どういうことを念頭に置かないといけないのか、
ということを知りたい趣旨でもあるので、こうしてご回答いただけるだけで勉強になります！
int型をnullにするのは扱いづらくなるので文字列の方がいいと思います
> int型をnullにするのは扱いづらくなる
よければこの点教えていただけますでしょうか？
ちょっと勘違いがありました。失礼しました。
数値がたの方が検索早いですね。
数値と文字列だと、数値の方が早いんですね。
よく聞くデータベースのチューニングというのはこういうやつの積み重ねなのだと理解しました。
ありがとうございます！
あなたの回答
tips
プレビュー
