5

I'm using QGIS 2.16.3 and attempting to convert a number of datetime fields that are appearing in the incorrect default GMT +0 timezone. I would like to add, for example, 10 hours to accurately reflect the time zone of data collection.

So far I've attempted using the field calculator:

"Date_Field" +   ('10:00:00')

which just adds a string to the end (i.e. '2017-4-06 14:00:001')

I've also tried:

( "Date_Field") + to_time('10:00:00')
( "Date_Field") + to_datetime('10:00:00')  
to_datetime( "Date_Field") + to_datetime('10:00:00')

which gives me an error "Cannot convert '2017-04-06 14:00:00' to double", although I am able to convert the datetime field alone.

This seems like a simple, common operation for anyone working with different time zones. My only workaround is to create a CSV and edit the fields in a spreadsheet.

  • How are you storing this information? Is your underlying DB compatible with timestamps or is the information simply kept as a string? – fabio.avigo Apr 17 '17 at 12:51
  • This can also help. – fabio.avigo Apr 17 '17 at 12:59
  • 2
    How about to_datetime("Date_Field") + to_interval('10 hours') ? – Kazuhito Apr 17 '17 at 13:08
  • I'm using a shapefile exported from a proprietary database but compatible with timestamps. When I created a new string field with to_datetime( "Date_Field" ) I am able to use Time Manager on that field but not do any calculation. – nickislander Apr 17 '17 at 13:24
  • 1
    Thanks @Kazuhito , to_interval successfully added 10 hours! – nickislander Apr 17 '17 at 13:27
9

Expression to_datetime("Date_Field") + to_interval('10 hours') will add 10 hours to the "Date_Field".

I have not tested fully, but it seems to_interval() accepts month(s) day(s) hour(s) and their combinations such as '1 day 2 hours'.

  • 1
    Thanks @Kazuhito , this worked perfectly. It also accepted the combination '10 hours 01 minutes' – nickislander Apr 17 '17 at 15:14
  • @nickislander I am glad it worked. Thanks for the minutes tip, too. – Kazuhito Apr 17 '17 at 16:24
  • 1
    Bravo @Kazuhito! A follow-up thought: If daylight savings time shifted during the period that the original data was collected (say the offset changed from -8 to -7), then the user would need to apply the expression twice - once for just those records in the -8 period, then a second calculation for the -7 period records. I have not tested, but this seems reasonable. – Stu Smith Sep 18 '18 at 18:01
  • Thanks @StuSmith. I agree with you. Perhaps keeping all of "UTC", "UTC-8h", "UTC-7h" fields in the attribute table will be helpful (and self-explanatory). Both time zones and DST time-shift are quite error-prone. – Kazuhito Sep 18 '18 at 20:09

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.