CODEWITHSUNDEEP
phpmysqldatedatetime
NorthernLights
NorthernLights

Reputation: 370

MySQL - saving date and time as DATETIME vs as saving date and time as String

I need to store a deadline, that consists of a date and a time (e.g 2016-05-02 19:02). I am currently using a field that has DATETIME as datatype but the problem is that its automatically saving it as 2016-05-02 19:02:00.

as a solution i was thinking to save the date in a String field.

So i am wondering if i should do that ? any performance advantages/disadvantages ?

Upvotes: 1

Views: 1328

Answers (2)

Juan
Juan

Reputation: 1426

As mentioned in the other answer, you should always use built-in data types whenever possible.

In your case, stick with DATETIME and then convert it to whatever format you need in the query using the DATE_FORMAT function, like so:

mysql> SELECT * FROM `mytable`;
+----+---------------------+
| id | mydatetime          |
+----+---------------------+
|  1 | 2016-06-06 14:12:00 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(`mydatetime`,'%b %d %Y %h:%i %p') AS `mydatetime` FROM `mytable`;
+----------------------+
| mydatetime           |
+----------------------+
| Jun 06 2016 02:12 PM |
+----------------------+
1 row in set (0.00 sec)

Reference: http://www.w3schools.com/sql/func_date_format.asp

Upvotes: 1

Torbjörn Stabo
Torbjörn Stabo

Reputation: 779

Don't fight the database. Use the builtin types unless you really need something that they can't offer(I'd say it's unlikely, though). (And by this I mean that you should use TIME, DATE or similar for times and dates. Then you can do calculations without having to convert values, etc)

If you don't need the seconds then just keep them 00 all the time.

Whether you use DATETIME, DATE & TIME or perhaps TIMESTAMP is up to you, how you use the data. Choose the alternative that makes most sense in your current situation.

Upvotes: 2

Related Questions