mysql timestampにつきまして

Question

お世話になります。
（目的）
・MYSQLにINSERTまたはUPDATEしたタイミングで日付を自動でカラムに入力する設定をしたいと考えております。
（現状）
・入力したい日付のフォーマットはyyyy-mm-ddなのですが、デフォルト値にtimestampを設定した場合、必ずhh-ssまで含まれてしまう仕様になっているようです。
・一方でdateに設定し、デフォルト値、設定をCurrent time stamp,On update Current_TimestampをするとERROR 1067 (42000): Invalid default value for ‘xxx’と怒られます。

（ご質問）
実際にyyyy-mm-ddをInsert,Updateの度に自動で入れていきたい場合には、どのような方法をとればよろしいのでしょうか。通常はphp側の関数で日付を出力し、mysqlに格納していく方法が一般的なのでしょうか？

よろしくお願い申し上げます。

Answer 1

INSERT、UPDATE時に明示的にCURRENT_TIMESTAMPを設定する方が、どこで現在日時が設定されるかが明白なため王道かと思いますが。

MySQLのマニュアルにあるように、

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

テーブル定義時に列制約として、
DEFAULT句、ON UPDATE句を指定すると質問者さんの望み通りの挙動となるのではないでしょうか？

###追記
更新される際の精度が問題なのですね、失礼しました^^;

CREATE TABLE t1(
  dt DATETIME DEFAULT NOW() ON UPDATE NOW()
);

上記ではどうでしょう？
時分秒までは記録されますがDATETIMEなら、
DATE_FORMAT関数などでYYYY-MM-DD書式の指定が可能かと思います。

Comments

[SugiuraY]

ご回答いただき、誠にありがとうございます。
初心者でありことを断りを入れるべきでした。
①つまりはカラムを定義する際に
データ型＞DATE
デフォルト値>CURRENT_TIMESTAMP
属性>on update CURRNET_TIMESTAMP
の様に取り扱うことは不可能ということでしょうか？

②また、DATE_FORMATはphp側に値を渡す時に整形するイメージをおっしゃていますでしょうか？

③しばしばMYSQLで明示的にと表現されますが、具体的にどの様な意味合いで使用される単語なのでしょうか？『なんとなく特定のカラムを指定して』といった意味合いかと解釈をしているのですが。

④最後になりますが、本来の最終的な目的は毎日自動でInsertされるデータについて、日付を割り当て、whereで日付＆idで初めてユニークとなり、一致するものをphpに出力することをイメージしています。その過程で書くレコードに日付を割り当てなければということで本質問に及んでいるのですが、そもそもこのようなアプローチは通常のプログラミングの試行にそうやり方でしょうか？
例えば以下のようなカラムのイメージです。
id:1date:2016/8/22 sale:300
id:2date:2016/8/22 sale:400
id:1date:2016/8/23 sale:200
id:3date:2016/8/23 sale:100

まだまだ不案内で申し訳ございません
そして、度重なる質問にお詫び申し上げます。
よろしくお願い申し上げます。

[Panzer_vor]

①質問者様の提示するパターンでは列制約として定義するの今の所は不可能です。
(今後できるようになるかもしれませんが)
無理やり似たようなことをするのであればテーブルへのトリガーとして実装するという方法はありそうです。

②質問者様の認識通りです。

③便利さから明示的を多用するのはよくないですね、すみません。今回の場合ですと質問者様の認識通りで、INSERT、UPDATE時に列指定した上で現在日を設定して下さいという意図です。
ただ「明示的」という言葉は文脈によって変わります。
SQLでよく使われるパターンだと「データ型の変換を明示的に行う」があります。
これはデータベース側でデータ変換を任せないで、
「SQL自体にデータ変換処理を記述してね」という意味で使われています。
(CASTなどがデータ変換用関数)

④IDと日付で一意ということ自体は違和感はありません。
ただそのケースだと気をつけてもらいたいのは、
1日に複数回同一ID情報を登録するパターンです。
この場合ですと、当方が提示したCREATE文では要件を満たせません(時分秒違いで新しく一意のデータとなってしまうため。)

なのでこのケースでは、
質問者様も挙げていますDATE型を使う方が適切かと思います。

それに加えて①の方法を取るには、トリガーなどを利用する手間が発生するので、
個人的にはPHP側で更新値を与えるか、
INSERT、UPDATEのクエリ内でCURDATE()として現在日を与える手段が良いと思われます。

[SugiuraY]

丁寧なお返事を頂き、深く、深く感謝を申し上げます。まさに、ご指摘の通り、1日に一回更新なので、hhssがあると一意にならないたもに嵌った事が本家の起点でした。
ご教示頂いた内容で全てクリアになりました！重ねて御礼申し上げます。
宜しくお願い申し上げます。

Answer 2

一度もやったことないので試してみて？の範囲だけど

cast(NOW() as date)

みたいにキャストしてINSERTしたらどうなるかな？

MySQLはストリクトモードじゃなければなんでもいい感じにしてくれるので、勝手にNOW()を変換して入れてくれそうな気もするけど…

Comments

[SugiuraY]

ご回答いただき、ありがとうございます。ちょっとハードルが高そうですが、調べてみます！

Answer 3

INSERTまたはUPDATEしたタイミングで日付を自動でカラムに入力する設定

普通は投入したいカラムを指定してCURDATE()などを渡してやるだけだと思いますが

Comments

[SugiuraY]

ご回答をいただき、ありがとうございます。そんな関数があるねですね、素人ですみません。大変勉強になりました、最もシンプルな方法かと思います。
よろしくお願い申し上げます。