How to obtain UTC timezone offset for date in the past for particular location?

[js29pub]

I am trying to figure out if there is a way to use ExifTool (or some other perl command) to obtain the UTC timezone offset for a date in the past for a particular location?  For example, suppose I have a date of "2014:08:12 17:30:00" and I want to know the timezone offset in "America/New_York" on that date.  The possible answers would be -04:00 or -05:00 (depending on daylight savings on that particular date).  Does anyone know a way to do this?

[Phil Harvey]

I don't know why you would want to use ExifTool for this, but the only way I could see doing this is to change the system locale time zone then set a FileModifyDate to whatever UTC time you want (add "Z" to the end of the date/time value when writing), then read it back again to see what the date/time is using the system locale.

- Phil

[StarGeek]

Is this for use in adding time zones to an image or just a general purpose command?

For just general purpose, you could use Curl and Google Maps API.  Convert the timestamp to unix epoch, get an API key (took me just a couple seconds to do it), and it returns the time zone offset in seconds in json.

If you're adding to images and on Windows, Geosetter will set TimeZoneOffset on files it processes.

[Alan Clifford]

I can do it in bash on a mac.  Presumably you could follow a similar logic in Perl;  I'll have a look after my game of golf - I'm not very good with Perl because I don't use it enough and keep forgetting it!


#!/bin/bash

datetime="2014:08:12 17:30:00"

TZ='America/New_York' ; export TZ

DTS_STAMP=`date  -j -f "%Y:%m:%d %H:%M:%S" "${datetime}" "+%Z %z"`

echo ${DTS_STAMP}

[js29pub]

Thanks for the suggestions so far.  Yeah, my goal is something I can do either within ExifTool directly or using some other CPAN module in perl.

Phil, you asked why I wanted to do this in ExifTool.  Let me clarify what I'm trying to do, and maybe you'll have a suggestion on a different approach.  The problem I'm trying to solve is that I have images/videos captured in the wrong timezone, and that Country/City timezone changes daylight savings on a different schedule (ie. different times each year) than the timezone I want to move the datetime's to.  So I can't use a fixed calculation for the necessary timezone integer offset.

I've only been successful in using ExifTool's Shift for specifying a precise timezone integer offset.  Essentially I was hoping it might also support timezone names (Country/City) in addition to timezone integer offsets, so that when using a timezone name (Country/City) then ExifTool would automatically figure out the correct offset (based off of the original date of the file) to shift to.

So my problem statement is: I need a way to modify only the timezone in datetime fields in images/videos in ExifTool to a specific timezone Country/City name rather than to a specific timezone integer offset.

So I'll give everyone's suggestions a try, but hopefully that clarifies the problem statement.

[Hayo Baan]

What you want is certainly possible (it's one of the many things I check/correct in my personal image info gathering tool) but involves some trickery. The starting point is the CPAN DateTime module together with DateTime::TimeZone. With that you can find the correct timezone (including active daylight savings, if any). The only real issue is finding the correct location from which to request the timzezone info.

Assuming you have your metadata set up fully you probably have the country and state filled in? All you need to do then is to map those to the correct timezone location e.g., America/New_York for the US state New York or Europe/London for the UK, etc.

Once you have that you can create the TimeZone object, setup the DateTime object with the date/time of the image, and get the active timezone offset using the offset_for_datetime method of the DateTime::TimeZone object.

Hope this helps,
Hayo

[Alan Clifford]

...  Presumably you could follow a similar logic in Perl;  I'll have a look after my game of golf ....

It's a much bigger project than first specified!

[l.willms]

I've only been successful in using ExifTool's Shift for specifying a precise timezone integer offset.  Essentially I was hoping it might also support timezone names (Country/City) in addition to timezone integer offsets, so that when using a timezone name (Country/City) then ExifTool would automatically figure out the correct offset (based off of the original date of the file) to shift to.

So my problem statement is: I need a way to modify only the timezone in datetime fields in images/videos in ExifTool to a specific timezone Country/City name rather than to a specific timezone integer offset.

Frankly, I don't grasp what you want.

You say that you successfully get the proper DST/non DST (Daylight Savings Time) offset using standard ExifTool means.

There is no specific offset per single cities. They are all in time zones.

Or is your problem that you know the place's name and want to find out in which time zone it is located?

[l.willms]

I don't know if this relates to your question, but ... does'nt Geosetter what you want to do?

[StarGeek]

Frankly, I don't grasp what you want.

You say that you successfully get the proper DST/non DST (Daylight Savings Time) offset using standard ExifTool means.

He can set it properly, but doesn't necessarily know the correct dates for Daylight Savings Time.  So it requires looking up the dates manually.  An automatic solution would be easier.

There is no specific offset per single cities. They are all in time zones.


Or is your problem that you know the place's name and want to find out in which time zone it is located?

I take it to mean that he has the County/City location set in the file, but doesn't have the actual geolocation.  For example, I know at least some Canon cameras have a Canon:TimeZoneCity tag that is set by a setting in the camera.  Assuming that he has some images where Daylight Savings Time wasn't set correctly in the camera, corrections could be made based upon the TimeZoneCity.

Additionally something to take note of is that Daylight Savings Time isn't constant for all cities in a time zone.  For example, even though Los Angeles and Tijuana are in Pacific Timezone, the US changed it's DST schedule in 2007, so there was a discrepancy.  Then, in 2009, Mexico allowed border cities to synchronize with the US.  More discrepancy. 

Of course, I could be wrong about all that.  But since he mentioned it, I've gone looking through some of my own images and come across places where I didn't have the Daylight Savings tag set properly and even knowing the the gps location, it's still a pain to figure out if some of them should have it set or not.

edit: Oh yeah, obligatory Tom Scott video on timezones.

[js29pub]

My issue does not have to do with geolocation issues.  Let me clarify by giving an example (with an MP4 file, but any image/video that ExifTool can process would equally apply):

Suppose the original MP4 recording has this CreationDateValue:

[XML]           CreationDateValue               : 2014:08:12 16:03:01+01:00

The UTC offset is +01, which is incorrect.  It should be either -04:00 or -05:00 in this example (since those are the possible options for America/New_York).

My goal is to take the CreationDateValue and write datetimes onto CreateDate/ModifyDate/etc using ExifTool's WriteInfo or SetFileModifyDate that have the appropriate timezone offset for America/New_York.  So I would need to write one or the other of these two possibilities:

[QuickTime]     CreateDate                      : 2014:08:12 16:03:01-04:00

or

[QuickTime]     CreateDate                      : 2014:08:12 16:03:01-05:00

But the question is: which one?  I want an automated way in perl to identify either -04:00 or -05:00 based on the date/time (in this example, "2014:08:12 16:03:01") of the original captured image/recording.

Ok, so good news!  Using Hayo's tip to use DateTime and DateTime::TimeZone, I was able to get it to work!

    my $datetime_str = "2014:08:12 16:03:01"

    my ($fdate,$ftime) = split(" ",$datetime_str);
    my ($fyear,$fmonth,$fday) = split(":",$fdate);
    my ($fhour,$fminute,$fsecond) = split(":",$ftime);

    my $dt = DateTime->new(
        year       => $fyear,
        month      => $fmonth,
        day        => $fday,
        hour       => $fhour,
        minute     => $fminute,
        second     => $fsecond,
        nanosecond => 000000000,
        time_zone  => 'America/New_York'   # I think this field does not matter here, it is only important in the TimeZone name
    );

    my $tz = DateTime::TimeZone->new(name => "America/New_York");
    my $tz_offset = $tz->offset_for_datetime( $dt );
    my $tz_hours = $tz_offset / 3600;
    print "tz_hours=$tz_hours\n";

Example tests:

$datetime_str = "2014:08:12 16:03:01"   (summer time, ie. daylight savings time)
Results in this: tz_hours=-4

$datetime_str = "2014:12:12 16:03:01"   (winter time, ie. daylight standard time)
Results in this: tz_hours=-5

And with tz_hours determined, I can now set up the SetNewValue for WriteInfo/SetFileModifyDate in ExifTool:

"2014:08:12 16:03:01-04:00"

"2014:12:12 16:03:01-05:00"

Thanks everyone for the help.  This solves my problem.  But feel free if people have suggestions on any more efficient ways to code the perl (for example, is there a more condensed way to get the $datetime_str into the $dt?)

The only other thing I was curious is if someone knows whether DateTime maintains a history (or obtains it from the OS) of all daylight savings schedule changes over the years, so that it will properly report the timezone for any arbitrary date in the past?  Or does it only use the current schedule defined by the OS and extrapolate that backwards (which would be unfortunate)?

And StarGeek, thanks for the reminder that not all cities have consistent schedules relative to each other even if they might currently be in the same UTC offset.  Fortunately in my case, the cities for my images/recordings are usually clearly tied to one of the named UTC cities, so I think I'm ok, but point taken.

[Hayo Baan]

Great, good to see you got my tip working!
To parse a date in one go, use something like this:

Code Select Expand

my $datetime_str = "2014:08:12 16:03:01";
my ($year,$month,$day,$hour,$minute,$second) = $datetime_str =~ /(\d{4}):(\d{2}):(\d{2}) (\d{2}):(\d{2}):(\d{2})/;

As far a I know DateTime does indeed keep a record of past daylight and time zone changes (at least for the dates I tried it with)

Note, since you are referencing the XML tag CreationDateValue, please be aware that exiftool currently is unable to change the value of that tag. If you want to change that, you'll have to e.g. use the matchDateTime script I wrote. You can find the script on GitHub (interestingly, I only added support for CreationDateValue yesterday...)