英文:
Golang: How to validate a MySQL timestamp string
问题
我们如何检查一个被认为是MySQL TIMESTAMP的字符串是否有效？使用的格式是：
YYYY-MM-DD HH:MM:SS[.fraction]，其中"fraction"有3位数字。
例如，2016-03-28 12:17:30.022应该是有效的。最好避免使用正则表达式，而是使用time.Parse()，但欢迎任何其他建议。
英文:
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.
答案1
得分: 7
使用类似于"2006-01-02 15:04:05.999"的布局字符串调用time.Parse函数来解析你的时间字符串。如果这样得到了一个有效的time.Time值且没有错误，那么你的字符串应该可以在数据库中使用。
timeStamp, err := time.Parse("2006-01-02 15:04:05.999", yourTimeString)
if err != nil {
// 处理错误...
}
// 处理timeStamp...
我不使用MySQL，但在PostgreSQL和Go之间，你可以直接传递时间戳和time.Time值，而无需转换为字符串...所以也许这可以简化你的问题。在Go中使用time.Parse将字符串转换为time.Time值，然后将该值写入数据库。
英文:
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.
答案2
得分: 2
你可以使用TIMESTAMP()函数将字符串转换为timestamp。如果可以转换为有效的时间戳，则为有效。如果转换为NULL，则不是有效的时间戳字符串。
使用方法如下：
TIMESTAMP('2016-03-28 12:17:30.022') IS NOT NULL
示例
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)
英文:
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)
答案3
得分: 1
如@Snowman在他的评论中指出的，一种解决方案是使用布局字符串和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!")
}
}
演示：https://play.golang.org/p/6bcciN_OAb
另请参阅：https://stackoverflow.com/questions/6589636/date-parsing-in-go
英文:
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
<br> Also check: https://stackoverflow.com/questions/6589636/date-parsing-in-go
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论