質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

データベースに日付のデータを格納したいけど年しかわからない、みたいな場合は?

解決済

回答 5

投稿

  • 評価
  • クリップ 4
  • VIEW 3,081

score 48

趣味で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サービスとかでこういうのがあると思いますが
ブクログがそうかと思って確認したんですが勘違いでした…どこだったんだろう…)、
一般的にこういう場合には内部でどのようにデータを持っているのでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hacosato

    2019/01/24 08:59

    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が出てくる
    といったことができるようにしたいのですが可能でしょうか?ということを伺いたいです!

    キャンセル

  • papinianus

    2019/01/24 09:11

    その手のサービスは使ったことないのですが、そのブクログにおいて、2018年のみという入力は可能ですか?可能でないとしたら質問者様はそういうときにどう入力していますか?

    キャンセル

  • hacosato

    2019/01/24 14:01

    papinianusさま
    実際にはブクログにその機能はありませんでした…。
    なにか別のサイトと勘違いしているような気がするのですが、思い出せませんでした…。すみません。
    https://i2.wp.com/www.ex-it-blog.com/wp-content/uploads/ex-it_81.png
    わたしのイメージとしては上記画像に近いです。
    年月日それぞれ分かれたメニューがあり、
    「日」の中には「1」「2」…「31」と選択肢が並ぶ最後に「年を指定しない」がある感じです。
    年月も日と同じです。

    キャンセル

回答 5

+11

日付の有効フラグを別途カラムを設置し、日付には1月1日を入れておけば
よいのでは?

日付 有効 備考
2019-01-24 0 日付として有効
2019-01-01 1 年月として有効
2019-01-01 2 年として有効

実際に年月や年として有効の場合日付はその範囲ないであればいつでも
いいのですが、存在しない可能性を考えると各月の1日や1月1日を
指定するのが確実です

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/24 14:09

    ありがとうございます。
    2018年で、月日がわからない場合、`2018-01-01` など実際に存在する日付を指定してしまうと、
    本当に1月1日を指定するのと区別がつかなくなってしまう…と悩んでいたんですが、
    有効かどうかを別のフラグで保持すればいいのですね。

    その場合、SELECTでざっくり2018年のデータを探す、などのときには
    細かいことを気にせず日付のカラムだけを見ればいいし、
    詳細が不明かどうかをきちんと把握する必要があるときには
    有効かどうかのフラグのカラムを確認すればいいということで、なんだかよさそう。

    キャンセル

checkベストアンサー

+4

yambejp様の回答がファイナルアンサーなんですが、弊害なども検討したいみたいなので。

  • 考えかた
    質問者様自身がユーザであるときに、どう入力しているか、です。仮にコメントで添付された画像のようなインプット欄縛りだったときに、じゃあ質問者様自身が「去年読んだんだよね~」っていうときにどう入れるかです。まあおおみそかとか正月とか入れるんじゃないですかね。それをDBに入れればいいと思います。

  • 1の問題点
    数値にわけたとき、31までしか入らないint型っていうのはあまり世間に流通してないので、32をエラーにするのがつらい。また3つのセルをあわせたときに2019/2/29や2018/4/31をエラーにするのがつらいです。
    アプリで頑張るのはもちろんなんですが、日付が妥当であることをDBのカラムの型が保証できない、のはつらいです。

  • 2の利点
    2の問題点の解決は、どこまで有効かをフラグでもつ解決がでてますのでそれとして、これが何がうれしいかって言うと、範囲検索がきくんですよね。去年読んだ本、先月読んだ本というのが、検索できる。
    そして、年しか指定しなかったものが、ソートしたときに、去年読んだ本の中で最初に来て欲しいなら1/1にすればいいし、年しか入ってないものは最後に来てほしいなら12/31を指定すればいい(月も同じ)
    逆にそういうことをしなくていい、日付なんていうのは参照目的のものでしかなくて、それでソートしたり検索したりするニーズが全くないなら、フラグ管理のほうがわずらわしいので、考え方1にするか、みたいな判断もあり得ると思います。
    (そういう意味では、極論、用途次第ってことにもなりますが)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/24 20:00

    > 2019/2/29や2018/4/31をエラーにするのがつらいです。
    たしかに大変やりづらそう…。
    Pythonを使って弾くことはやればできるかもしれませんが、SQLで完結するほうが好ましいですね。

    > そういう意味では、極論、用途次第ってことにもなります
    用途次第というのはその通りかもな、というのは質問を立てた時点でなんとなく思ってはいました。
    単に日付を記録したいだけで、今後一生ソートも検索もしないなら、全部文字列でデータを持っていればいいですよね。
    ただ、曖昧なデータは曖昧なりに、そうでないはっきりしたデータははっきりしたなりに
    ソートや検索をして活用したい、混在したデータの範囲内でできるだけ活用したい、
    と思ったときに、どういうデータの持ち方がいいのかをうかがいたく質問しました。
    また、その場合「活用」をどう定義したらエンジニアさんの思考をトレースできるのか、
    という点にも興味があり質問しました。
    だいぶクリアになったように思います。ありがとうございます。

    キャンセル

+2

一般的かどうかは知りませんが、私は2ですね。但し数値型です。
なぜそうしているかを経緯を交えて説明します。

最初の頃は私は、日付は日付型として管理する方が日付型に関する色々な関数が直接使えるし効率が良いと考えていました。

ただ、期間を指定するような場合に、開始~終了に無期限のような設定は行えず9999/12/31とか、別に無期限用のフラグを持たせるというのはなんとなくしっくりこなかったのです。

ある時、自治体のデータを扱うことがあって、その時のデータは項目が分かれており、月や日が'不明'として入力されていました。
年・月・日を分けた上でさらに判断が必要になるのは明らかに効率が悪そうです。
困った私は、数値型でyyyymmddという形式でmmやddは00や99を不明などに割り当てるという方法を試してみました。

これが、意外に最強でした。
日付の書式に変換が必要な場面はありましたが、それは画面や帳票やCSVなどに出力する場合に単に書式を設定するような類です。
例えばyyyy/mm/ddの様な書式なのですが、書式というだけで日付型に変換する必要はありません。
数値型に書式を設定するので、0000/00/00というようにするだけです。

逆に、SQLでの結合や抽出の条件するような場合に判定をする必要が無くこれが非常に効率的でした。
開始~終了の様な期間で無制限は0~99999999と設定すればよく、判定が必要な場合も値で可能ですしね。

あ、文字型にするのは数値型より遅くなるのでハナから考えていませんでした。
という訳で、日付を扱うのは数値型最強説でした。
但し、ログのようなデータ(作成日とか更新日とかの記録系)は日付型の方が良いので、制御に使う項目に限定ですけど。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/26 11:00 編集

    リンク先の回答とは「型に関係なく書式を揃えるだけ」という意味ではマッチしているのはそうなのですが、伝えたいのは、「数値型最強」なので文字型ではないです。
    数値なのでlikeは使えません。月だけで絞るというのは商を使用します。
    文字型より数値型で扱う事に意義があるのです。

    キャンセル

  • 2019/01/26 11:03

    >・実在する日付かどうかを調べるのが難しい
    >・日付同士の引き算などが難しい
    やってみれば分かるのですけど、入れ子が一つ増えるくらいですよ。
    例:日付関数(formatなどの日付書式への変換関数)

    キャンセル

  • 2019/01/26 16:33

    > 数値なのでlikeは使えません。月だけで絞るというのは商を使用します。
    あっそうか…おっしゃる通りですね。
    割り算(小数点以下を切り捨て)で日の部分をなくして、
    割り算の余りを使えば年の部分をなくして月の部分だけを残せる!

    年を0000にして数値にしたら桁数が揃わないのでは…?
    とか思ったんですが、考えてみたら揃ってなくても構わないんですよね。
    揃えることにこだわるなら、年が不明の場合に9999にするなどすればいいですね〜。

    再度コメントいただきありがとうございます!

    キャンセル

0

あまりよい作りではないですが、年月日をそれぞれ4桁2桁2桁の別個のカラムにして(NULL OK)それぞれ保存する。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/01/24 19:55

    > int型をnullにするのは扱いづらくなる
    よければこの点教えていただけますでしょうか?

    キャンセル

  • 2019/01/25 10:45

    ちょっと勘違いがありました。失礼しました。
    数値がたの方が検索早いですね。

    キャンセル

  • 2019/01/26 00:06

    数値と文字列だと、数値の方が早いんですね。
    よく聞くデータベースのチューニングというのはこういうやつの積み重ねなのだと理解しました。
    ありがとうございます!

    キャンセル

0

私なら、1900/01/01 を 1日目として、整数で何日目かを保存します。UNIX時間の日付版のようなものだと考えてください。
年月しかわからない場合はその月の1日、年しかわからない場合はその年の1/1の日付にし、そのデータの確度(有効フラグのようなもの)を別カラムに持つようにします。
「確度」といったのは、例えば年しかわからない場合は取る値の範囲が+0〜+365(+366)なのでその範囲の最大値をいれるかなぁと思ったからです。なぜ取りうる日数の範囲にしたかというと、将来的に、曖昧には覚えてるが、「第何週」とか「季節」とかで覚えてるみたいな需要がある場合にも対応できるからです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る