Converting string to datetime

Question

I'm trying to enter a date in a mysql table

`ssdate` datetime

The function that produces the date can output it in 3 formats:

1276142400000
Thu Jun 10 00:00:00 GMT-0400 2010
Fri Jun 4 2010

I'm wondering which of these would be easiest to convert to this field format? Since I'm trying to save only the date, is there a better option than datetime that would work with one of these output formats?

Answer 1

Hi,

You can set your column as a varchar(14), will work perfectly with your first format output.

Answer 2

The easiest way would probably be FROM_UNIXTIME(), but 1276142400000 does not appear to be a Unix timestamp:

mysql> SELECT FROM_UNIXTIME(1276142400000);
+------------------------------+
| FROM_UNIXTIME(1276142400000) |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.00 sec)

Perhaps it's a Unix timestamp mutiplied by 1000:

mysql> SELECT FROM_UNIXTIME(1276142400000/1000);
+-----------------------------------+
| FROM_UNIXTIME(1276142400000/1000) |
+-----------------------------------+
| 2010-06-10 06:00:00               |
+-----------------------------------+
1 row in set (0.05 sec)

And, if unsure, you always have STR_TO_DATE():

mysql> SELECT STR_TO_DATE('Fri Jun 4 2010', '%a %b %e %Y');
+----------------------------------------------+
| STR_TO_DATE('Fri Jun 4 2010', '%a %b %e %Y') |
+----------------------------------------------+
| 2010-06-04                                   |
+----------------------------------------------+
1 row in set (0.00 sec)

Answer 3

You can use the third format:

date('Y-m-d H:i:s', strtotime('Fri Jun 4 2010'));

Just put the result in your datetime field. If you're going to use a date field instead you can do

date('Y-m-d', strtotime('Fri Jun 4 2010'));

Answer 4

In database I would store time values in in DATETIME field, mainly because of built-in methods for date manipulations (INTERVAL, etc.). One possible alternative is to store UNIX timestamp as numeric value, but I wouldn't recommend that. If in doubt, choose standard solutions.

As for date format to convert from, I would definitely go with UNIX timestamp (#1 option in your case, multiplied by 1000 I guess) as most universal one. All other formats are locale- and timezone-dependent, which is a possible source for a lots of headaches in the future.