MySQL 日期时间与时间戳关于如何记录/显示时间

Question

我刚刚在测试运行中注意到这一点

    mysql> select * from enumdecoder;
+----+---------------------+---------------------+------+--------------------+-------+-------+------+----------+

| ID | CreateStamp         | ModStamp            | Flag | className          | value | label | abv  | sequence |
+----+---------------------+---------------------+------+--------------------+-------+-------+------+----------+
|  1 | 2015-04-22 03:11:17 | 2015-04-22 15:11:18 |    0 | TestElementFactory | ONE   | NULL  | One  |       10 |
|  2 | 2015-04-22 03:11:17 | 2015-04-22 15:11:18 |    0 | TestElementFactory | TWO   | NULL  | Two  |       20 |
|  3 | 2015-04-22 03:11:17 | 2015-04-22 15:11:18 |    0 | TestElementFactory | THREE | NULL  | Thre |       30 |
|  4 | 2015-04-22 03:11:18 | 2015-04-22 15:11:18 |    0 | NULL               | NULL  | NULL  | NULL |        0 |
+----+---------------------+---------------------+------+--------------------+-------+-------+------+----------+

该表的定义如下:

CREATE TABLE enumdecoder (
    ID bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    CreateStamp datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
    ModStamp timestamp,
    Flag bigint(20) NOT NULL DEFAULT 0,
    className char(254),
    value char(50),
    label char(50),
    abv char(5),
    sequence smallint(3) SIGNED NOT NULL DEFAULT 0
);

如果你想知道为什么默认是因为我目前正在努力让 Heroku 与播放框架很好地配合(它声称可以开箱即用)，Heroku 不允许使用多个列有自动更新时间戳，也不允许触发器。我已经重写了我的核心数据库库来容纳它们，并且默认设置将使任何漏掉的东西都像拇指酸痛一样突出。

Answer 1

啊-在写这个问题的时候就回答了!

将以下内容定义为时间源:

val sqlfmt = DateTimeFormat.forPattern("yyyy-MM-dd hh:mm:ss")

我真正需要的当然是......

val sqlfmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")
def now = new DateTime()
def sqlnow = sqlfmt.print(now)

很高兴我在下午注意到了这一点!

现在我得到了预期的结果:

mysql> select * from enumdecoder;
+----+---------------------+---------------------+------+--------------------+-------+-------+------+----------+
| ID | CreateStamp         | ModStamp            | Flag | className          | value | label | abv  | sequence |
+----+---------------------+---------------------+------+--------------------+-------+-------+------+----------+
|  1 | 2015-04-22 15:18:40 | 2015-04-22 15:18:41 |    0 | TestElementFactory | ONE   | NULL  | One  |       10 |
|  2 | 2015-04-22 15:18:40 | 2015-04-22 15:18:41 |    0 | TestElementFactory | TWO   | NULL  | Two  |       20 |
|  3 | 2015-04-22 15:18:40 | 2015-04-22 15:18:41 |    0 | TestElementFactory | THREE | NULL  | Thre |       30 |
|  4 | 2015-04-22 15:18:41 | 2015-04-22 15:18:41 |    0 | NULL               | NULL  | NULL  | NULL |        0 |
+----+---------------------+---------------------+------+--------------------+-------+-------+------+----------+
4 rows in set (0.00 sec)

我想无论如何我都会发帖以防万一你今天需要一些东西来笑:)