nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Preserving a MySql datetime field in Python

JoeBorgione

I have a python script that extracts data from a MySql database and writes it to a csv. The original table is a mix of all sorts of field types including datetime and timestamp. Once the csv is written and I perform an an arcpy table to table to get it into a fgdb, the datetime fields automagically become text fields. Not so good if you need to filter by date....

CSV files are both a boon and bane: is there a way to go right from MySql to a File geodatabse, thus preserving the field types? Unfortunately I do not have admin rights to our e-gdb (Sql server back-end) so I can't go MySql to SqlServer....

Find more posts tagged with

Accepted answers

All comments

bharold

Oh my word, a doc bug! Thanks for reporting it, I'll send it on.

Data Interoperability is an extension like Network Analyst, Spatial Analyst etc., you can evaluate it at no cost.

JoeBorgione

Hey Bruce Harold I just went to the ArcGIS Interoperabilty page, clicked on the Learn More link and it sent me to an ArcGIS Help 10.2, 20.21, and 10.2.2 page....

JoeBorgione

I've got a couple of humongous databases to plow through. It just might save not only time, but my sanity!

bharold

OK full disclosure I'm the Product Manager for Data Interoperability 😉

JoeBorgione

Thanks Bruce Harold!

bharold

All, this pain is totally avoidable, just obtain the Data Interoperability extension and go directly from MySQL to File GDB. In Data Interop the reader is called MariaDB, in fact there are two, one spatial, one not. Bouncing data through CSV will throw the schema overboard as even writing a schema.ini file can't handle all data types.

JoeBorgione

It's a little more complicated that that: these tables get geocoded, and work their way into a published service which is then consumed by an Operations Dashboard app. Ultimately, it's in OD where I need to filter by date.

DanPatterson_Retired

I added to my post, YYYY-MM-DD is universal format great for sorting. If you need to parse by month, you can split on the string's - separator

JoeBorgione

In mySql the values (when not Null) are:

2019-07-08 10:59:11

Bruce Harold‌ provides: https://community.esri.com/community/open-platform-standards-and-interoperability/blog/2019/09/26/using-odbc-data-sources-in-core-arcgis-pro which discusses odbc connections: my 64 bit windows 10 desktop has:

And the mySql site ( MySQL :: Download Connector/ODBC ) offers a 32 bit and a 64 bit driver. So I'm a little confused as to which odbc driver to down load and for that matter how to use pyodbc to traverse it. (Installing pyodbc package as I type this...)

DanPatterson_Retired

s <SPAN class="operator token">=</SPAN> <SPAN class="string token">"2020-01-15 23:15:30"</SPAN>  <SPAN class="comment token"># --- universal</SPAN>

<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>datetime<SPAN class="punctuation token">.</SPAN>datetime<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>time<SPAN class="punctuation token">.</SPAN>strptime<SPAN class="punctuation token">(</SPAN>s<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="number token">2020</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">01</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">15</SPAN> <SPAN class="number token">23</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">15</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">30</SPAN>

t <SPAN class="operator token">=</SPAN> <SPAN class="string token">"01-15-2020 23:15:30"</SPAN>  <SPAN class="comment token"># mon, day year</SPAN>

<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>datetime<SPAN class="punctuation token">.</SPAN>datetime<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>time<SPAN class="punctuation token">.</SPAN>strptime<SPAN class="punctuation token">(</SPAN>t<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"%m-%d-%Y %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="number token">2020</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">01</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">15</SPAN> <SPAN class="number token">23</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">15</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">30</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

YYYY-MM-DD date format is it?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.