Date-format conversion

Question

I'm using Time Manager and QGIS 1.8.0 and I am stuck at date-format conversion. Time Manager needs Gregorian format YYYY-MM-DD HH:MM:SS.ssssss in text field and I have thousands of records in "month/day/year hour:minute:second" format (Julian format). As I'm not a GIS expert I need some directions to make this conversion correctly and be happy with Time Manager presentations.

Answer 1

I have not used the Time Manager plugin either, but I think @Pedro is correct, this is a task for a field calculator first. Once you create the new field and fill it, then use the Time Manager to act upon the new data.
In this case, Python might make this an easier operation, given that it has some pretty powerful string operators. The default field calculator in QGIS does not use python, however, there is a processing tool called "Advanced Python field calculator".

Look under the Processing menu, and select Toolbox. It will open the list shown below.

It opens up the following window. Note that the input layer and options for a new field have been filled out. In addition, the code has been placed in the lower window, which will be applied to each row in the dataset. You reference a field by placing the name inside carets, like so: <field name>
One drawback of this tool seems to be that it will not output to an existing layer, but that may not actually be the case. Documentation is a bit slim as it is a recent addition to the core product. The new table can be joined to the existing one and simply use the basic field calculator to copy the data over as necessary.

Advanced Python field calculator window:

Input Data:

Output Data:

Here is an explanation of the code.

1. You need to split based on the Space in the timestamp to return the date portion. This returns the date portion of the timestamp which comes before the space(' ').
   Code: timestampparts = <dt_temp>.split(' ')
   Example: timestampparts = '04/01/2014 14:20:45:0000'.split(' ')
   Result: timestampparts = ('04/01/2014','14:20:45:0000')

2. The second split parses the date into sections based on the location of the slash('/').
   Code: dateparts = timestampparts[0].split('/')
   Example: dateparts = '04/01/2014'.split('/')
   Result: dateparts = ('04','01','2014')

3. The next step is to build a new date string. The first 0 or 1 in each section of the string below represents the argument in the format function. The numbers beside those are returning the items within that variable. Simply reorder them as necessary to have the parts of the date in your desired order.
   Code: newdate = '{0[2]}-{0[0]}-{0[1]} {1[1]}'.format(dateparts,timestampparts)
   Example: newdate = '{0[2]}-{0[0]}-{0[1]} {1[1]}'.format(('04','01','2014'),('04/01/2014','14:20:45:0000'))
   Result: newdate = '2014-04-01 14:20:45:0000'

There may be more compact ways of doing this, including making it all one line of code. I chose to break it out because that gives you the ability to work with each piece individually. Whether you use each piece here or not, it gives you the tools to parse text more efficiently.

Check out the Python website for more info: Python

Hat tip to @underdark and her blog post about the Advanced Python Field Calculator

Edit ---------

Based on your question regarding adding the time component back in, I used the same script, but added minutes and hours to the source data. The calculator script as it is now, will take any existing time component and append it to the date in the new format. You thus should be able to maintain the hours as you need to. If you want to do something different like strip out just the hours and include them in a timestamp with no minutes, seconds, etc., that requires some additional processing of the time component. I'm including a sample of that below as well.

Date with time component included:

Code block to strip out hour component of time and create new reformatted timestamp:

Result showing date reformatted and time with only hour portion carried over:

Answer 2

More simple than other solutions, use Field Calculator functions (QGIS 2.18 maybe under): to_date(string) and format_date(datetime, format)

1. If '/' use regexp_replace( '2012/05/04' ,'/','-')
2. to_date('2012-05-04') → 2012-05-04
3. format_date('2012-05-15','dd.MM.yyyy') → '15.05.2012'

If you have problem with international date format you can use substring to convert:

format_date( to_date(  '20'||substr( "date_field" ,7,2) ||'-'||substr( "date_field" ,4,2) ||'-'||substr("date_field" ,1,2)),'yyyy-MM-dd')

Answer 3

Sergio, I never used the time manager plugin, but this seems to be a job for the table manager (or other dbase editor, if you wish). You will need to parse the data using string parsing functions in the attribute table manager. You can do everything in a single pass, or create fields for year, month, day, hour, minute and second, and then combine them.

For example,

Month would be equal to:

substr("julian_date_field",1, strpos("julian_date_field",'/'))

Day would be equal to:

substr("julian_date_field",strpos("julian_date_field",'/')+1,
strpos(right("julian_date_field",strpos("julian_date_field",'/')+1),'/'))

You see that it gets continuously complex, but if you do it by parts (parsing and writing the results to fields), you can get it done a lot faster.