I have an SQL table, with a column, and each row contains a string value such as this:
Fri, 31 Oct 2014 01:51:22 +0000
How can I convert it to a normal, d-m-Y date, using Python? If it's not possible, can I do it in MySQL? I tried to use PHPMyAdmin data conversion feature, but it didn't recognize it as a date.
EDIT, tried to do this, and I get a Null value in return.
SELECT str_to_date(`date_created`, 'DD-MM-YYYY') FROM `orders` WHERE id = "1"
Python Solution:
>>> from datetime import datetime
>>> date_string = 'Fri, 31 Oct 2014 01:51:22 +0000'
>>> format_string = '%a, %d %b %Y %I:%M:%S %z'
>>>
>>> date_object = datetime.strptime(date_string, format_string)
>>> date_object
datetime.datetime(2014, 10, 31, 1, 51, 22, tzinfo=datetime.timezone.utc)
>>> date_object.strftime("%d-%m-%Y")
'31-10-2014'
>>>
MySQL Solution:
mysql> set @date_string := 'Fri, 31 Oct 2014 01:51:22 +0000';
Query OK, 0 rows affected (0.00 sec)
mysql> set @format_string = '%a, %d %b %Y %I:%i:%S %x';
Query OK, 0 rows affected (0.00 sec)
mysql> select @dt:=str_to_date( @date_string, @format_string ) as dt,
-> date_format( @dt, '%d-%m-%Y' ) as ddmmyy;
+---------------------+------------+
| dt | ddmmyy |
+---------------------+------------+
| 2014-10-31 01:51:22 | 31-10-2014 |
+---------------------+------------+
1 row in set, 1 warning (0.00 sec)
Warning must be on timezone as the format is not recognized.
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1292
Message: Truncated incorrect datetime value: 'Fri, 31 Oct 2014 01:51:22 +0000'
1 row in set (0.00 sec)
Refer to:
Since you already have a date data I think you can convert it into dd-mm-yyy format date in an easy way like this
>>> str = "Fri, 31 Oct 2014 01:51:22 +0000"
>>> str = str [5:16]
>>> str.replace(" ","-")
'31-Oct-2014'
hope this is what you are looking for.
str.replace(" ","-") . it should work. If not please post the piece of code which You implemented.I'm not sure if this is the most elegant solution, but this should work:
datestring = "Fri, 31 Oct 2014 01:51:22 +0000"
(dump, day, month, year, dump, dump) = datestring.split()
You can simply ignore dump and day, month and year will contain the respective elements of the date.
You can convert string to struct_time first and then format it in a way you want
>>> import time
>>> time.strptime('Fri, 31 Oct 2014 01:51:22 +0000','%a, %d %b %Y %H:%M:%S +0000')
time.struct_time(tm_year=2014, tm_mon=10, tm_mday=31, tm_hour=1, tm_min=51, tm_sec=22, tm_wday=4, tm_yday=304, tm_isdst=-1)
Also, you can use %z in the format string for time zone, but this may not work on all platforms\Python versions.
SELECTthis value from MySQL? Maybe you could transform it using a MySQL date function.