douba9776
2016-03-29 15:07 阅读 182

Golang:如何验证MySQL时间戳字符串

How can we check that a string considered to be a MySQL TIMESTAMP is actually valid? The format used is:

YYYY-MM-DD HH:MM:SS[.fraction] where "fraction" has 3 digits.

For example 2016-03-28 12:17:30.022 should be valid. Preferably I would like to avoid regex and use time.Parse() but any other suggestion is welcome.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    doujun1495 doujun1495 2016-03-30 13:09

    Call time.Parse with a layout string such as "2006-01-02 15:04:05.999" on your time string. If that results in a valid time.Time value and no error then your string should work in the db.

    timeStamp, err := time.Parse("2006-01-02 15:04:05.999", yourTimeString)
        if err != nil {
            // do something with err...
        }
    // do something with timeStamp...
    

    I don't use MySQL but between PostgreSQL and Go you can pass around timestamps and time.Time values without converting to strings... so maybe that simplifies your problem. Covert the string in Go using time.Parse and then write the time.Time value to the db.

    点赞 评论 复制链接分享
  • dpi10335 dpi10335 2016-03-29 15:10

    You can use TIMESTAMP() to convert the string to timestamp. If it can be converted to a valid timestamp, it is valid. If converted to NULL, then it is not a void timestamp string.

    Use:

    TIMESTAMP('2016-03-28 12:17:30.022') IS NOT NULL
    

    Demo

    mysql> select TIMESTAMP('2016-03-28 12:17:30.022'), TIMESTAMP('2016-03-28 12:17:300.022');
    +--------------------------------------+---------------------------------------+
    | TIMESTAMP('2016-03-28 12:17:30.022') | TIMESTAMP('2016-03-28 12:17:300.022') |
    +--------------------------------------+---------------------------------------+
    | 2016-03-28 12:17:30.022              | NULL                                  |
    +--------------------------------------+---------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select TIMESTAMP('2016-03-28 12:17:30.022') IS NOT NULL, TIMESTAMP('2016-03-28 12:17:300.022') IS NOT NULL;
    +--------------------------------------------------+---------------------------------------------------+
    | TIMESTAMP('2016-03-28 12:17:30.022') IS NOT NULL | TIMESTAMP('2016-03-28 12:17:300.022') IS NOT NULL |
    +--------------------------------------------------+---------------------------------------------------+
    |                                                1 |                                                 0 |
    +--------------------------------------------------+---------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    点赞 评论 复制链接分享
  • dongshen2903 dongshen2903 2016-03-30 07:35

    As @Snowman pointed out in his comment, one solution is by using a layout string and time.Parse()

    package main
    
    import (
      "fmt"
      "time"
    )
    
    func main() {
    
      timestamp    := "2016-03-28 11:50:50.476"
      const layout  = "2006-01-02 03:04:05.999"
    
      _, error := time.Parse(layout, timestamp)
    
      if error != nil {
        fmt.Println(error)
      } else {
        fmt.Println("valid!")
      }
    }
    

    Demo: https://play.golang.org/p/6bcciN_OAb
    Also check: Date parsing in Go

    点赞 评论 复制链接分享

相关推荐