Menu
I am trying to convert datetime into timestamp but mysql is giving me warnings and also converted values are wrong. Here is SQL query
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(STR_TO_DATE(A.date_added, '%M %d %Y %h:%i%p')) WHERE A.id=B.id; Warnings
+---------+------+--------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------+ | Warning | 1411 | Incorrect datetime value: '2011-06-11 20:29:02' for function str_to_date | +---------+------+--------------------------------------------------------------------------+ Result
+---------------------+---------------------+ | date_added | date_added | +---------------------+---------------------+ | 2012-02-23 06:12:45 | 2012-12-23 19:08:33 | +---------------------+---------------------+ I also tried following query but it shows 0000-00-00 00:00:00 in timestamp field.
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id; 
811
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' . The TIMESTAMP data type is used for values that contain both date and time parts.
Here is the query to convert from datetime to date in MySQL. mysql> select cast(ArrivalDatetime as Date) as Date from ConvertDateTimeToDate; The following is the output.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id;
UNIX_TIMESTAMP('2015-01-15 12:00:00'); is sufficient to convert a mysql datetime to a Timestamp.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
