CODEWITHSUNDEEP
mysqlsqldatabasedatetimesolr
Jimmy
Jimmy

Reputation: 12487

Storing specific datetime format in database

This is the datetime format SOLR requires:

1995-12-31T23:59:59Z

When I try to store this as a datetime field in MySQL it says it isn't valid. My question is, ideally I would want to keep a datetime in my database so I can sort by date rather than just making it a varchar. How is it best to do this in my circumstance?

Upvotes: 1

Views: 938

Answers (2)

AbsoluteƵERØ
AbsoluteƵERØ

Reputation: 7870

It would need to look like '1995-12-31 23:59:59' to be stored in the database as a DATETIME field without throwing an error.

If you needed to pull it out of the database in the form you're presenting you can always do something like:

select DATE_FORMAT(timestamp_field,'%Y-%m-%dT%TZ') as solrDate from table;

Upvotes: 6

MikeTWebb
MikeTWebb

Reputation: 9279

You could use the DATETIME data type. The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

See this article: DATE, TIME, DATETIME, TIMESTAMP data types

Upvotes: 0

Related Questions