6

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.

11

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. QGIS Processing Toolbox

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:
Advanced Python field calculator

Input Data:
Input Data

Output Data:
Output Layer

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: Date and time component using existing script

Code block to strip out hour component of time and create new reformatted timestamp: Code Block to reformat date and strip out hour portion of time

Result showing date reformatted and time with only hour portion carried over: Formatted date, hour portion of time carryover

  • Plain text files and csv files are not generally editable as they are not very robust storage formats in a GIS sense.I would suggest saving the layer to a shapefile or some other spatial data format, then you should be able to edit it with no problem. – Get Spatial Aug 22 '14 at 22:33
  • @SérgioFerreira - Instead of copying the log as a comment, please edit your question and add it there. What you were trying to put in the comment was mostly cut off, so it is hard to figure out the problem. – Get Spatial Aug 29 '14 at 16:56
  • Sergio, again, I cannot see the log message. Please edit your question and add the message from the log to the bottom of your question. If possible, add screen shots of step by step what you are trying so that it is easier to figure out where the problem is occurring. Also, what is your input data format? Is it a shapefile, PostGIS, etc? – Get Spatial Aug 29 '14 at 21:10
  • The error message you got says to look in the log for more details. Did you look at the log file? – Get Spatial Sep 2 '14 at 8:29
  • I got progress here but in the result of "newdate" variable the second portion of variable (hours) doesn't appear in the table field (newdate output I got is: '2014-04-01 14' instead newdate = '2014-04-01 14:20:45:0000'). I change some arguments codes to try fix It but whithout sucess. I have 24 records for each day and the hours is necessary to keep coordinates events in sequence. @get – Sérgio Ferreira Sep 10 '14 at 18:31
3

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')
  • I tried the third option but it didn't work (it returned null). I think it works only for string fields. Can I do the third option for a date field? – ilias m Jul 11 '18 at 17:09
  • Can you send me an exemple file with a link? – jlSta Jul 12 '18 at 10:17
  • Of course..1drv.ms/u/s!Atq-s6oCoYOzjA3WoeIUg2tl8gnp the field is called CALLDATE and its type is date. The date format which I want is dd/mm//yyyy – ilias m Jul 12 '18 at 10:29
  • @iliasm This works perfectly format_date( "CALLDATE" , 'dd/mm/yyyy') don't forget to choose date in place of integer for the new field – jlSta Jul 12 '18 at 11:28
  • it didn't work..look the image 1drv.ms/u/s!Atq-s6oCoYOzjA5qectkJdsBFnbR ..what version of qgis do you use? I use the 3.0 version.. – ilias m Jul 12 '18 at 12:39
1

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.

  • Hello Pedro, I was looking inside table manager and I don't figured out how to parse table, as you told me, using table manager. Could you help me again? – Sérgio Ferreira Aug 21 '14 at 1:40
  • Sure, Sergio. I believe the best way of learning it might be with a video. try this youtube.com/watch?v=YyzBt97Sibo and this youtube.com/watch?v=_AdKekz35P4 Let me know if they don't help enough – Pedro Camargo Aug 21 '14 at 16:40
  • Should I put the code you sent me inside the conversion function (tostring)? Of course, before It I have to fix the edit tools problem after import data from a CSV and Text file. – Sérgio Ferreira Aug 22 '14 at 21:18
  • Yes, but I guess is would be even easier to do it in excel. I was assuming the info was on a layer, so excel (2007+) does not save dbf. But if it is in csv, just put in excel and save it as the right format (or parse it the same way in excel). – Pedro Camargo Aug 22 '14 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.