0

I have a a bunch of files whose ctime and mtime are based on local time. I am using pathlib object to parse through these files.

How can I convert them to datetime format in UTC time so I can upload them via Pandas to sql server. What I have so far:

{"filecreatedatetime_utc": etest_file_Pobj.stat().st_ctime_ns,
"fileupdatedatetime_utc": etest_file_Pobj.stat().st_mtime_ns}

filemeta_df[["filecreatedatetime_utc"]] = filemeta_df[["filecreatedatetime_utc"]].apply(pd.to_datetime)
filemeta_df[["fileupdatedatetime_utc"]] = filemeta_df[["fileupdatedatetime_utc"]].apply(pd.to_datetime)
result = filemeta_df.to_sql('t_etl_etest_file_meta', schema='log', con=db_dict['engine'], if_exists='append', index=False)
FObersteiner
  • 22,500
  • 8
  • 42
  • 72
edo101
  • 629
  • 6
  • 17
  • Are you sure that the timestamps refer to *local time* (including time zone UTC offset and DST if applicable)? `pandas` can easily convert ns since the epoch to_datetime but local time will be a bit more tricky... – FObersteiner Aug 02 '20 at 20:40
  • @MrFuppes According to my mentor, the ctime and mtime I get from the files come from the Pacific Time which is what the local time is in where the files are. Other than that, I have no idea how to ensure that it is local time. Or that it is indeed PST. But I am in PST and each time I create a file out of this script, it seems to read to PST – edo101 Aug 02 '20 at 20:45
  • Ok, I 'd suggest you verify that anyway because it might save you a lot of work. `ctime` and `mtime` normally refer to UTC, although your PC might convert it to local time *for display*. – FObersteiner Aug 02 '20 at 20:48
  • Well I am runnign Python on a Windows environement and the location of the files are in a Windows environment. Is ctime and mtime, by default processed in UTC even in a Windows environement or is it only UTC in a UNIX environment? @MrFuppes – edo101 Aug 02 '20 at 20:52
  • yes, that is the normal return value even on Windows ;-) If you use e.g. `pd.to_datetime`, note that you can set keyword `utc=True`, so this will directly give you an aware datetime (UTC). – FObersteiner Aug 02 '20 at 21:18
  • @MrFuppes do you have proof that it exhibits this behaviour in Windows? – edo101 Aug 03 '20 at 02:19
  • you can find that info in the [os.path docs](https://docs.python.org/3/library/os.path.html), which is what `pathlib` falls back to. Also interesting for you might be [this SO question](https://stackoverflow.com/questions/237079/how-to-get-file-creation-modification-date-times-in-python). And of course you can just create a file on your machine, extract ctime / mtime of it and see if it is seconds since the epoch (UTC). – FObersteiner Aug 03 '20 at 06:04

0 Answers0