Cockroachdb format datetime in select query

2
votes

I'm trying to format a timestamp value to yyyymmdd in cockroach DB select query.

In MySQL, we can format a date using DATE_FORMAT()

E.g. SELECT DATE_FORMAT(created_at, "%Y-%m-%d") FROM users to fetch result into a desired format.

What would be an alternative of DATE_FORMAT() we can use in cockroach DB?

2
cockroachdbsql-timestamp

2 Answers

2
votes

You can use the experimental builtin experimental_strftime which uses the strftime syntax.

SELECT experimental_strftime(created_at, '%Y-%m-%d') FROM users

Alternatively, you can use experimental_strptime which uses the strptime syntax.

Please take the time to read the important notes about experimental features. The time formatting builtins are experimental because they behave differently on different platforms.

0
votes

I am using golang, gorm and cockroachdb. I tried experimental_strftime.But it creates invalid query.

query := `SELECT experimental_strftime(created_at, '%Y-%m-%d') FROM users`
var version []string
rows, err := store.DB.Raw(query).Rows()
if err != nil {
    return version, err
}
defer rows.Close()
for rows.Next() {
    err := rows.Scan(&version)
    return version, err
}

Query generated in debug mode is SELECT experimental_strftime(created_at, '%!Y(MISSING)-%!m(MISSING)-%!d(MISSING)') FROM users