Is it possible to calculate a local date from a UTC time representing a local midnight?

Question

Imagine you take a local date, say 2016-12-28, in a given time zone, say America/New_York, and convert the start of that date into UTC (in this case 2016-12-28T05:00:00Z).

Is it then possible from that UTC time to get back to the original local date without knowing the time zone? All you know is that the UTC time represents some local midnight/start of day.

I imagine this is possible in certain scenarios, e.g. when the offset is rather small, but I'm not sure that there won't be two possible answers when time zones are close to the date line, i.e. when two time zones have the same time, but different dates/offsets (-10 and +14).

(This problem was originally encountered in a database where local dates where wrongly stored in UTC, and the original time zone data is difficult to retrieve again.)

Answer 1

It may be possible under certain constraints to identify the time zone offset (UTC-05:00), but not the original time zone (America/New_York). You could only list the possible time zones that the offset might belong to at that moment, as Howard showed in his answer. There are other edge cases that make this problem difficult:

• You gave a very clear case of how one couldn't determine the date for offsets close to the International Date Line.

  • For example, consider 2016-12-31T10:00:00Z. That could be 2016-12-31T00:00:00-10:00 (possibly Pacific/Honolulu), or it could be 2017-01-01T00:00:00+14:00 (possibly Pacific/Tongatapu).

  • Both -10/+14 and -11/+13 pairings could be possible, but no inhabited places on Earth actually use -12. So if you have values that are at exactly noon, they are likely +12, unless you are dealing with ships at sea.

• The local midnight value might not exist in the time zone you're expecting.

  • For example, 2016-10-16T03:00:00Z is the start of the day in both America/Sao_Paulo and America/Bahia (both in Brazil). However, America/Sao_Paulo's local time is 01:00, not 00:00. There is no midnight on that day, due to their DST spring-forward transition.

• The local midnight value might exist twice in the time zone you're expecting.

  • For example, in America/Havana, both 2016-11-06T04:00:00Z and 2016-11-06T05:00:00Z have a local time of 00:00, due to their DST fall-back transition.

So, in the general case, you may be able to resolve most of these back to their original offset, but you will have ambiguities for time zones with -10, -11, +13, or +14 offsets, and for time zones with DST transitions at midnight (in spring) or at 1:00 AM (in fall). (Keep in mind spring/fall are different between northern/southern hemisphere.)

Answer 2

Using this free, open-source C++ library, I can compute a list of possible timezones for this problem.

Update I've rewritten the driver for this code to really explore an entire day's worth of results, and compared it to the specific examples in Matt's excellent answer.

Here's the code:

#include <iostream>
#include <vector>

template <class Duration>
std::vector<date::zoned_time<std::common_type_t<Duration, std::chrono::seconds>>>
find_by_offset(date::sys_time<Duration> tp, const std::chrono::seconds& offset)
{
    using namespace std::chrono;
    using namespace date;
    std::vector<zoned_time<std::common_type_t<Duration, std::chrono::seconds>>> results;
    auto& db = get_tzdb();
    for (auto& z : db.zones)
    {
        if (z.get_info(tp).offset == offset)
            results.push_back(make_zoned(&z, tp));
    }
    return results;
}

int
main()
{
    using namespace date;
    using namespace std::chrono;
    for (auto offset = -15h; offset <= 13h; offset += 1h)
    {
        auto tp = sys_days{2016_y/12/28} + offset;
        std::cout << "These are all the timezones it is midnight at " << format("%F %T %Z\n", tp);
        auto d0 = round<days>(tp);
        auto dm1 = d0 - days{1};
        auto dp1 = d0 + days{1};
        auto v = find_by_offset(tp, dm1 - tp);
        for (auto const& zt : v)
            std::cout << format("%F %T %Z %z ", zt) << zt.get_time_zone()->name() << '\n';
        v = find_by_offset(tp, d0 - tp);
        for (auto const& zt : v)
            std::cout << format("%F %T %Z %z ", zt) << zt.get_time_zone()->name() << '\n';
        v = find_by_offset(tp, dp1 - tp);
        for (auto const& zt : v)
            std::cout << format("%F %T %Z %z ", zt) << zt.get_time_zone()->name() << '\n';
        std::cout << '\n';
    }
}

The code creates a UTC timestamp and offset pair and feeds that to find_by_offset which loops over all the timezones, and queries each timezone for its offset at that UTC timestamp. If the timezone's offset matches the desired offset, a zoned_time is added to the results (a zoned_time is a pairing of a timezone with a timestamp).

Here are all of the results which might have a midnight on the date 2016-12-28:

These are all the timezones it is midnight at 2016-12-27 09:00:00 UTC
2016-12-27 00:00:00 AKST -0900 America/Anchorage
2016-12-27 00:00:00 AKST -0900 America/Juneau
2016-12-27 00:00:00 AKST -0900 America/Metlakatla
2016-12-27 00:00:00 AKST -0900 America/Nome
2016-12-27 00:00:00 AKST -0900 America/Sitka
2016-12-27 00:00:00 AKST -0900 America/Yakutat
2016-12-27 00:00:00 -09 -0900 Etc/GMT+9
2016-12-27 00:00:00 GAMT -0900 Pacific/Gambier

These are all the timezones it is midnight at 2016-12-27 10:00:00 UTC
2016-12-27 00:00:00 HST -1000 America/Adak
2016-12-27 00:00:00 -10 -1000 Etc/GMT+10
2016-12-27 00:00:00 HST -1000 HST
2016-12-27 00:00:00 HST -1000 Pacific/Honolulu
2016-12-27 00:00:00 CKT -1000 Pacific/Rarotonga
2016-12-27 00:00:00 TAHT -1000 Pacific/Tahiti
2016-12-28 00:00:00 +14 +1400 Etc/GMT-14
2016-12-28 00:00:00 WSDT +1400 Pacific/Apia
2016-12-28 00:00:00 LINT +1400 Pacific/Kiritimati
2016-12-28 00:00:00 +14 +1400 Pacific/Tongatapu

These are all the timezones it is midnight at 2016-12-27 11:00:00 UTC
2016-12-27 00:00:00 -11 -1100 Etc/GMT+11
2016-12-27 00:00:00 NUT -1100 Pacific/Niue
2016-12-27 00:00:00 SST -1100 Pacific/Pago_Pago
2016-12-28 00:00:00 +13 +1300 Etc/GMT-13
2016-12-28 00:00:00 NZDT +1300 Pacific/Auckland
2016-12-28 00:00:00 PHOT +1300 Pacific/Enderbury
2016-12-28 00:00:00 TKT +1300 Pacific/Fakaofo
2016-12-28 00:00:00 FJST +1300 Pacific/Fiji

These are all the timezones it is midnight at 2016-12-27 12:00:00 UTC
2016-12-27 00:00:00 -12 -1200 Etc/GMT+12
2016-12-28 00:00:00 +12 +1200 Asia/Anadyr
2016-12-28 00:00:00 +12 +1200 Asia/Kamchatka
2016-12-28 00:00:00 +12 +1200 Etc/GMT-12
2016-12-28 00:00:00 TVT +1200 Pacific/Funafuti
2016-12-28 00:00:00 MHT +1200 Pacific/Kwajalein
2016-12-28 00:00:00 MHT +1200 Pacific/Majuro
2016-12-28 00:00:00 NRT +1200 Pacific/Nauru
2016-12-28 00:00:00 GILT +1200 Pacific/Tarawa
2016-12-28 00:00:00 WAKT +1200 Pacific/Wake
2016-12-28 00:00:00 WFT +1200 Pacific/Wallis

These are all the timezones it is midnight at 2016-12-27 13:00:00 UTC
2016-12-28 00:00:00 +11 +1100 Antarctica/Casey
2016-12-28 00:00:00 MIST +1100 Antarctica/Macquarie
2016-12-28 00:00:00 +11 +1100 Asia/Magadan
2016-12-28 00:00:00 +11 +1100 Asia/Sakhalin
2016-12-28 00:00:00 +11 +1100 Asia/Srednekolymsk
2016-12-28 00:00:00 AEDT +1100 Australia/Currie
2016-12-28 00:00:00 AEDT +1100 Australia/Hobart
2016-12-28 00:00:00 LHDT +1100 Australia/Lord_Howe
2016-12-28 00:00:00 AEDT +1100 Australia/Melbourne
2016-12-28 00:00:00 AEDT +1100 Australia/Sydney
2016-12-28 00:00:00 +11 +1100 Etc/GMT-11
2016-12-28 00:00:00 BST +1100 Pacific/Bougainville
2016-12-28 00:00:00 VUT +1100 Pacific/Efate
2016-12-28 00:00:00 SBT +1100 Pacific/Guadalcanal
2016-12-28 00:00:00 KOST +1100 Pacific/Kosrae
2016-12-28 00:00:00 NFT +1100 Pacific/Norfolk
2016-12-28 00:00:00 NCT +1100 Pacific/Noumea
2016-12-28 00:00:00 PONT +1100 Pacific/Pohnpei

These are all the timezones it is midnight at 2016-12-27 14:00:00 UTC
2016-12-28 00:00:00 +10 +1000 Antarctica/DumontDUrville
2016-12-28 00:00:00 +10 +1000 Asia/Ust-Nera
2016-12-28 00:00:00 +10 +1000 Asia/Vladivostok
2016-12-28 00:00:00 AEST +1000 Australia/Brisbane
2016-12-28 00:00:00 AEST +1000 Australia/Lindeman
2016-12-28 00:00:00 +10 +1000 Etc/GMT-10
2016-12-28 00:00:00 CHUT +1000 Pacific/Chuuk
2016-12-28 00:00:00 ChST +1000 Pacific/Guam
2016-12-28 00:00:00 PGT +1000 Pacific/Port_Moresby

These are all the timezones it is midnight at 2016-12-27 15:00:00 UTC
2016-12-28 00:00:00 +09 +0900 Asia/Chita
2016-12-28 00:00:00 TLT +0900 Asia/Dili
2016-12-28 00:00:00 WIT +0900 Asia/Jayapura
2016-12-28 00:00:00 +09 +0900 Asia/Khandyga
2016-12-28 00:00:00 KST +0900 Asia/Seoul
2016-12-28 00:00:00 JST +0900 Asia/Tokyo
2016-12-28 00:00:00 +09 +0900 Asia/Yakutsk
2016-12-28 00:00:00 +09 +0900 Etc/GMT-9
2016-12-28 00:00:00 PWT +0900 Pacific/Palau

These are all the timezones it is midnight at 2016-12-27 16:00:00 UTC
2016-12-28 00:00:00 BNT +0800 Asia/Brunei
2016-12-28 00:00:00 CHOT +0800 Asia/Choibalsan
2016-12-28 00:00:00 HKT +0800 Asia/Hong_Kong
2016-12-28 00:00:00 +08 +0800 Asia/Irkutsk
2016-12-28 00:00:00 MYT +0800 Asia/Kuala_Lumpur
2016-12-28 00:00:00 MYT +0800 Asia/Kuching
2016-12-28 00:00:00 CST +0800 Asia/Macau
2016-12-28 00:00:00 WITA +0800 Asia/Makassar
2016-12-28 00:00:00 PHT +0800 Asia/Manila
2016-12-28 00:00:00 CST +0800 Asia/Shanghai
2016-12-28 00:00:00 SGT +0800 Asia/Singapore
2016-12-28 00:00:00 CST +0800 Asia/Taipei
2016-12-28 00:00:00 ULAT +0800 Asia/Ulaanbaatar
2016-12-28 00:00:00 AWST +0800 Australia/Perth
2016-12-28 00:00:00 +08 +0800 Etc/GMT-8

These are all the timezones it is midnight at 2016-12-27 17:00:00 UTC
2016-12-28 00:00:00 +07 +0700 Antarctica/Davis
2016-12-28 00:00:00 ICT +0700 Asia/Bangkok
2016-12-28 00:00:00 +07 +0700 Asia/Barnaul
2016-12-28 00:00:00 ICT +0700 Asia/Ho_Chi_Minh
2016-12-28 00:00:00 HOVT +0700 Asia/Hovd
2016-12-28 00:00:00 WIB +0700 Asia/Jakarta
2016-12-28 00:00:00 +07 +0700 Asia/Krasnoyarsk
2016-12-28 00:00:00 +07 +0700 Asia/Novokuznetsk
2016-12-28 00:00:00 +07 +0700 Asia/Novosibirsk
2016-12-28 00:00:00 WIB +0700 Asia/Pontianak
2016-12-28 00:00:00 +07 +0700 Asia/Tomsk
2016-12-28 00:00:00 +07 +0700 Etc/GMT-7
2016-12-28 00:00:00 CXT +0700 Indian/Christmas

These are all the timezones it is midnight at 2016-12-27 18:00:00 UTC
2016-12-28 00:00:00 +06 +0600 Antarctica/Vostok
2016-12-28 00:00:00 +06 +0600 Asia/Almaty
2016-12-28 00:00:00 +06 +0600 Asia/Bishkek
2016-12-28 00:00:00 BDT +0600 Asia/Dhaka
2016-12-28 00:00:00 +06 +0600 Asia/Omsk
2016-12-28 00:00:00 +06 +0600 Asia/Qyzylorda
2016-12-28 00:00:00 BTT +0600 Asia/Thimphu
2016-12-28 00:00:00 XJT +0600 Asia/Urumqi
2016-12-28 00:00:00 +06 +0600 Etc/GMT-6
2016-12-28 00:00:00 IOT +0600 Indian/Chagos

These are all the timezones it is midnight at 2016-12-27 19:00:00 UTC
2016-12-28 00:00:00 +05 +0500 Antarctica/Mawson
2016-12-28 00:00:00 +05 +0500 Asia/Aqtau
2016-12-28 00:00:00 +05 +0500 Asia/Aqtobe
2016-12-28 00:00:00 +05 +0500 Asia/Ashgabat
2016-12-28 00:00:00 +05 +0500 Asia/Atyrau
2016-12-28 00:00:00 +05 +0500 Asia/Dushanbe
2016-12-28 00:00:00 PKT +0500 Asia/Karachi
2016-12-28 00:00:00 +05 +0500 Asia/Oral
2016-12-28 00:00:00 +05 +0500 Asia/Samarkand
2016-12-28 00:00:00 +05 +0500 Asia/Tashkent
2016-12-28 00:00:00 +05 +0500 Asia/Yekaterinburg
2016-12-28 00:00:00 +05 +0500 Etc/GMT-5
2016-12-28 00:00:00 +05 +0500 Indian/Kerguelen
2016-12-28 00:00:00 MVT +0500 Indian/Maldives

These are all the timezones it is midnight at 2016-12-27 20:00:00 UTC
2016-12-28 00:00:00 +04 +0400 Asia/Baku
2016-12-28 00:00:00 GST +0400 Asia/Dubai
2016-12-28 00:00:00 +04 +0400 Asia/Tbilisi
2016-12-28 00:00:00 +04 +0400 Asia/Yerevan
2016-12-28 00:00:00 +04 +0400 Etc/GMT-4
2016-12-28 00:00:00 +04 +0400 Europe/Astrakhan
2016-12-28 00:00:00 +04 +0400 Europe/Samara
2016-12-28 00:00:00 +04 +0400 Europe/Saratov
2016-12-28 00:00:00 +04 +0400 Europe/Ulyanovsk
2016-12-28 00:00:00 SCT +0400 Indian/Mahe
2016-12-28 00:00:00 MUT +0400 Indian/Mauritius
2016-12-28 00:00:00 RET +0400 Indian/Reunion

These are all the timezones it is midnight at 2016-12-27 21:00:00 UTC
2016-12-28 00:00:00 EAT +0300 Africa/Khartoum
2016-12-28 00:00:00 EAT +0300 Africa/Nairobi
2016-12-28 00:00:00 +03 +0300 Antarctica/Syowa
2016-12-28 00:00:00 AST +0300 Asia/Baghdad
2016-12-28 00:00:00 +03 +0300 Asia/Famagusta
2016-12-28 00:00:00 AST +0300 Asia/Qatar
2016-12-28 00:00:00 AST +0300 Asia/Riyadh
2016-12-28 00:00:00 +03 +0300 Etc/GMT-3
2016-12-28 00:00:00 +03 +0300 Europe/Istanbul
2016-12-28 00:00:00 +03 +0300 Europe/Kirov
2016-12-28 00:00:00 +03 +0300 Europe/Minsk
2016-12-28 00:00:00 MSK +0300 Europe/Moscow
2016-12-28 00:00:00 MSK +0300 Europe/Simferopol
2016-12-28 00:00:00 +03 +0300 Europe/Volgograd

These are all the timezones it is midnight at 2016-12-27 22:00:00 UTC
2016-12-28 00:00:00 EET +0200 Africa/Cairo
2016-12-28 00:00:00 SAST +0200 Africa/Johannesburg
2016-12-28 00:00:00 CAT +0200 Africa/Maputo
2016-12-28 00:00:00 EET +0200 Africa/Tripoli
2016-12-28 00:00:00 WAST +0200 Africa/Windhoek
2016-12-28 00:00:00 EET +0200 Asia/Amman
2016-12-28 00:00:00 EET +0200 Asia/Beirut
2016-12-28 00:00:00 EET +0200 Asia/Damascus
2016-12-28 00:00:00 EET +0200 Asia/Gaza
2016-12-28 00:00:00 EET +0200 Asia/Hebron
2016-12-28 00:00:00 IST +0200 Asia/Jerusalem
2016-12-28 00:00:00 EET +0200 Asia/Nicosia
2016-12-28 00:00:00 EET +0200 EET
2016-12-28 00:00:00 +02 +0200 Etc/GMT-2
2016-12-28 00:00:00 EET +0200 Europe/Athens
2016-12-28 00:00:00 EET +0200 Europe/Bucharest
2016-12-28 00:00:00 EET +0200 Europe/Chisinau
2016-12-28 00:00:00 EET +0200 Europe/Helsinki
2016-12-28 00:00:00 EET +0200 Europe/Kaliningrad
2016-12-28 00:00:00 EET +0200 Europe/Kiev
2016-12-28 00:00:00 EET +0200 Europe/Riga
2016-12-28 00:00:00 EET +0200 Europe/Sofia
2016-12-28 00:00:00 EET +0200 Europe/Tallinn
2016-12-28 00:00:00 EET +0200 Europe/Uzhgorod
2016-12-28 00:00:00 EET +0200 Europe/Vilnius
2016-12-28 00:00:00 EET +0200 Europe/Zaporozhye

These are all the timezones it is midnight at 2016-12-27 23:00:00 UTC
2016-12-28 00:00:00 CET +0100 Africa/Algiers
2016-12-28 00:00:00 CET +0100 Africa/Ceuta
2016-12-28 00:00:00 WAT +0100 Africa/Lagos
2016-12-28 00:00:00 WAT +0100 Africa/Ndjamena
2016-12-28 00:00:00 CET +0100 Africa/Tunis
2016-12-28 00:00:00 CET +0100 CET
2016-12-28 00:00:00 +01 +0100 Etc/GMT-1
2016-12-28 00:00:00 CET +0100 Europe/Amsterdam
2016-12-28 00:00:00 CET +0100 Europe/Andorra
2016-12-28 00:00:00 CET +0100 Europe/Belgrade
2016-12-28 00:00:00 CET +0100 Europe/Berlin
2016-12-28 00:00:00 CET +0100 Europe/Brussels
2016-12-28 00:00:00 CET +0100 Europe/Budapest
2016-12-28 00:00:00 CET +0100 Europe/Copenhagen
2016-12-28 00:00:00 CET +0100 Europe/Gibraltar
2016-12-28 00:00:00 CET +0100 Europe/Luxembourg
2016-12-28 00:00:00 CET +0100 Europe/Madrid
2016-12-28 00:00:00 CET +0100 Europe/Malta
2016-12-28 00:00:00 CET +0100 Europe/Monaco
2016-12-28 00:00:00 CET +0100 Europe/Oslo
2016-12-28 00:00:00 CET +0100 Europe/Paris
2016-12-28 00:00:00 CET +0100 Europe/Prague
2016-12-28 00:00:00 CET +0100 Europe/Rome
2016-12-28 00:00:00 CET +0100 Europe/Stockholm
2016-12-28 00:00:00 CET +0100 Europe/Tirane
2016-12-28 00:00:00 CET +0100 Europe/Vienna
2016-12-28 00:00:00 CET +0100 Europe/Warsaw
2016-12-28 00:00:00 CET +0100 Europe/Zurich
2016-12-28 00:00:00 MET +0100 MET

These are all the timezones it is midnight at 2016-12-28 00:00:00 UTC
2016-12-28 00:00:00 GMT +0000 Africa/Abidjan
2016-12-28 00:00:00 GMT +0000 Africa/Accra
2016-12-28 00:00:00 GMT +0000 Africa/Bissau
2016-12-28 00:00:00 WET +0000 Africa/Casablanca
2016-12-28 00:00:00 WET +0000 Africa/El_Aaiun
2016-12-28 00:00:00 GMT +0000 Africa/Monrovia
2016-12-28 00:00:00 GMT +0000 America/Danmarkshavn
2016-12-28 00:00:00 +00 +0000 Antarctica/Troll
2016-12-28 00:00:00 WET +0000 Atlantic/Canary
2016-12-28 00:00:00 WET +0000 Atlantic/Faroe
2016-12-28 00:00:00 WET +0000 Atlantic/Madeira
2016-12-28 00:00:00 GMT +0000 Atlantic/Reykjavik
2016-12-28 00:00:00 GMT +0000 Etc/GMT
2016-12-28 00:00:00 UCT +0000 Etc/UCT
2016-12-28 00:00:00 UTC +0000 Etc/UTC
2016-12-28 00:00:00 GMT +0000 Europe/Dublin
2016-12-28 00:00:00 WET +0000 Europe/Lisbon
2016-12-28 00:00:00 GMT +0000 Europe/London
2016-12-28 00:00:00 WET +0000 WET

These are all the timezones it is midnight at 2016-12-28 01:00:00 UTC
2016-12-28 00:00:00 EGT -0100 America/Scoresbysund
2016-12-28 00:00:00 AZOT -0100 Atlantic/Azores
2016-12-28 00:00:00 CVT -0100 Atlantic/Cape_Verde
2016-12-28 00:00:00 -01 -0100 Etc/GMT+1

These are all the timezones it is midnight at 2016-12-28 02:00:00 UTC
2016-12-28 00:00:00 FNT -0200 America/Noronha
2016-12-28 00:00:00 BRST -0200 America/Sao_Paulo
2016-12-28 00:00:00 GST -0200 Atlantic/South_Georgia
2016-12-28 00:00:00 -02 -0200 Etc/GMT+2

These are all the timezones it is midnight at 2016-12-28 03:00:00 UTC
2016-12-28 00:00:00 BRT -0300 America/Araguaina
2016-12-28 00:00:00 ART -0300 America/Argentina/Buenos_Aires
2016-12-28 00:00:00 ART -0300 America/Argentina/Catamarca
2016-12-28 00:00:00 ART -0300 America/Argentina/Cordoba
2016-12-28 00:00:00 ART -0300 America/Argentina/Jujuy
2016-12-28 00:00:00 ART -0300 America/Argentina/La_Rioja
2016-12-28 00:00:00 ART -0300 America/Argentina/Mendoza
2016-12-28 00:00:00 ART -0300 America/Argentina/Rio_Gallegos
2016-12-28 00:00:00 ART -0300 America/Argentina/Salta
2016-12-28 00:00:00 ART -0300 America/Argentina/San_Juan
2016-12-28 00:00:00 ART -0300 America/Argentina/San_Luis
2016-12-28 00:00:00 ART -0300 America/Argentina/Tucuman
2016-12-28 00:00:00 ART -0300 America/Argentina/Ushuaia
2016-12-28 00:00:00 PYST -0300 America/Asuncion
2016-12-28 00:00:00 BRT -0300 America/Bahia
2016-12-28 00:00:00 BRT -0300 America/Belem
2016-12-28 00:00:00 AMST -0300 America/Campo_Grande
2016-12-28 00:00:00 GFT -0300 America/Cayenne
2016-12-28 00:00:00 AMST -0300 America/Cuiaba
2016-12-28 00:00:00 BRT -0300 America/Fortaleza
2016-12-28 00:00:00 WGT -0300 America/Godthab
2016-12-28 00:00:00 BRT -0300 America/Maceio
2016-12-28 00:00:00 PMST -0300 America/Miquelon
2016-12-28 00:00:00 UYT -0300 America/Montevideo
2016-12-28 00:00:00 SRT -0300 America/Paramaribo
2016-12-28 00:00:00 BRT -0300 America/Recife
2016-12-28 00:00:00 BRT -0300 America/Santarem
2016-12-28 00:00:00 CLST -0300 America/Santiago
2016-12-28 00:00:00 CLST -0300 Antarctica/Palmer
2016-12-28 00:00:00 -03 -0300 Antarctica/Rothera
2016-12-28 00:00:00 FKST -0300 Atlantic/Stanley
2016-12-28 00:00:00 -03 -0300 Etc/GMT+3

These are all the timezones it is midnight at 2016-12-28 04:00:00 UTC
2016-12-28 00:00:00 AST -0400 America/Barbados
2016-12-28 00:00:00 AST -0400 America/Blanc-Sablon
2016-12-28 00:00:00 AMT -0400 America/Boa_Vista
2016-12-28 00:00:00 VET -0400 America/Caracas
2016-12-28 00:00:00 AST -0400 America/Curacao
2016-12-28 00:00:00 AST -0400 America/Glace_Bay
2016-12-28 00:00:00 AST -0400 America/Goose_Bay
2016-12-28 00:00:00 AST -0400 America/Grand_Turk
2016-12-28 00:00:00 GYT -0400 America/Guyana
2016-12-28 00:00:00 AST -0400 America/Halifax
2016-12-28 00:00:00 BOT -0400 America/La_Paz
2016-12-28 00:00:00 AMT -0400 America/Manaus
2016-12-28 00:00:00 AST -0400 America/Martinique
2016-12-28 00:00:00 AST -0400 America/Moncton
2016-12-28 00:00:00 AST -0400 America/Port_of_Spain
2016-12-28 00:00:00 AMT -0400 America/Porto_Velho
2016-12-28 00:00:00 AST -0400 America/Puerto_Rico
2016-12-28 00:00:00 AST -0400 America/Santo_Domingo
2016-12-28 00:00:00 AST -0400 America/Thule
2016-12-28 00:00:00 AST -0400 Atlantic/Bermuda
2016-12-28 00:00:00 -04 -0400 Etc/GMT+4

These are all the timezones it is midnight at 2016-12-28 05:00:00 UTC
2016-12-28 00:00:00 EST -0500 America/Atikokan
2016-12-28 00:00:00 COT -0500 America/Bogota
2016-12-28 00:00:00 EST -0500 America/Cancun
2016-12-28 00:00:00 EST -0500 America/Detroit
2016-12-28 00:00:00 ACT -0500 America/Eirunepe
2016-12-28 00:00:00 ECT -0500 America/Guayaquil
2016-12-28 00:00:00 CST -0500 America/Havana
2016-12-28 00:00:00 EST -0500 America/Indiana/Indianapolis
2016-12-28 00:00:00 EST -0500 America/Indiana/Marengo
2016-12-28 00:00:00 EST -0500 America/Indiana/Petersburg
2016-12-28 00:00:00 EST -0500 America/Indiana/Vevay
2016-12-28 00:00:00 EST -0500 America/Indiana/Vincennes
2016-12-28 00:00:00 EST -0500 America/Indiana/Winamac
2016-12-28 00:00:00 EST -0500 America/Iqaluit
2016-12-28 00:00:00 EST -0500 America/Jamaica
2016-12-28 00:00:00 EST -0500 America/Kentucky/Louisville
2016-12-28 00:00:00 EST -0500 America/Kentucky/Monticello
2016-12-28 00:00:00 PET -0500 America/Lima
2016-12-28 00:00:00 EST -0500 America/Nassau
2016-12-28 00:00:00 EST -0500 America/New_York
2016-12-28 00:00:00 EST -0500 America/Nipigon
2016-12-28 00:00:00 EST -0500 America/Panama
2016-12-28 00:00:00 EST -0500 America/Pangnirtung
2016-12-28 00:00:00 EST -0500 America/Port-au-Prince
2016-12-28 00:00:00 ACT -0500 America/Rio_Branco
2016-12-28 00:00:00 EST -0500 America/Thunder_Bay
2016-12-28 00:00:00 EST -0500 America/Toronto
2016-12-28 00:00:00 EST -0500 EST
2016-12-28 00:00:00 EST -0500 EST5EDT
2016-12-28 00:00:00 -05 -0500 Etc/GMT+5
2016-12-28 00:00:00 EASST -0500 Pacific/Easter

These are all the timezones it is midnight at 2016-12-28 06:00:00 UTC
2016-12-28 00:00:00 CST -0600 America/Bahia_Banderas
2016-12-28 00:00:00 CST -0600 America/Belize
2016-12-28 00:00:00 CST -0600 America/Chicago
2016-12-28 00:00:00 CST -0600 America/Costa_Rica
2016-12-28 00:00:00 CST -0600 America/El_Salvador
2016-12-28 00:00:00 CST -0600 America/Guatemala
2016-12-28 00:00:00 CST -0600 America/Indiana/Knox
2016-12-28 00:00:00 CST -0600 America/Indiana/Tell_City
2016-12-28 00:00:00 CST -0600 America/Managua
2016-12-28 00:00:00 CST -0600 America/Matamoros
2016-12-28 00:00:00 CST -0600 America/Menominee
2016-12-28 00:00:00 CST -0600 America/Merida
2016-12-28 00:00:00 CST -0600 America/Mexico_City
2016-12-28 00:00:00 CST -0600 America/Monterrey
2016-12-28 00:00:00 CST -0600 America/North_Dakota/Beulah
2016-12-28 00:00:00 CST -0600 America/North_Dakota/Center
2016-12-28 00:00:00 CST -0600 America/North_Dakota/New_Salem
2016-12-28 00:00:00 CST -0600 America/Rainy_River
2016-12-28 00:00:00 CST -0600 America/Rankin_Inlet
2016-12-28 00:00:00 CST -0600 America/Regina
2016-12-28 00:00:00 CST -0600 America/Resolute
2016-12-28 00:00:00 CST -0600 America/Swift_Current
2016-12-28 00:00:00 CST -0600 America/Tegucigalpa
2016-12-28 00:00:00 CST -0600 America/Winnipeg
2016-12-28 00:00:00 CST -0600 CST6CDT
2016-12-28 00:00:00 -06 -0600 Etc/GMT+6
2016-12-28 00:00:00 GALT -0600 Pacific/Galapagos

These are all the timezones it is midnight at 2016-12-28 07:00:00 UTC
2016-12-28 00:00:00 MST -0700 America/Boise
2016-12-28 00:00:00 MST -0700 America/Cambridge_Bay
2016-12-28 00:00:00 MST -0700 America/Chihuahua
2016-12-28 00:00:00 MST -0700 America/Creston
2016-12-28 00:00:00 MST -0700 America/Dawson_Creek
2016-12-28 00:00:00 MST -0700 America/Denver
2016-12-28 00:00:00 MST -0700 America/Edmonton
2016-12-28 00:00:00 MST -0700 America/Fort_Nelson
2016-12-28 00:00:00 MST -0700 America/Hermosillo
2016-12-28 00:00:00 MST -0700 America/Inuvik
2016-12-28 00:00:00 MST -0700 America/Mazatlan
2016-12-28 00:00:00 MST -0700 America/Ojinaga
2016-12-28 00:00:00 MST -0700 America/Phoenix
2016-12-28 00:00:00 MST -0700 America/Yellowknife
2016-12-28 00:00:00 -07 -0700 Etc/GMT+7
2016-12-28 00:00:00 MST -0700 MST
2016-12-28 00:00:00 MST -0700 MST7MDT

These are all the timezones it is midnight at 2016-12-28 08:00:00 UTC
2016-12-28 00:00:00 PST -0800 America/Dawson
2016-12-28 00:00:00 PST -0800 America/Los_Angeles
2016-12-28 00:00:00 PST -0800 America/Tijuana
2016-12-28 00:00:00 PST -0800 America/Vancouver
2016-12-28 00:00:00 PST -0800 America/Whitehorse
2016-12-28 00:00:00 -08 -0800 Etc/GMT+8
2016-12-28 00:00:00 PST -0800 PST8PDT
2016-12-28 00:00:00 PST -0800 Pacific/Pitcairn

These are all the timezones it is midnight at 2016-12-28 09:00:00 UTC
2016-12-28 00:00:00 AKST -0900 America/Anchorage
2016-12-28 00:00:00 AKST -0900 America/Juneau
2016-12-28 00:00:00 AKST -0900 America/Metlakatla
2016-12-28 00:00:00 AKST -0900 America/Nome
2016-12-28 00:00:00 AKST -0900 America/Sitka
2016-12-28 00:00:00 AKST -0900 America/Yakutat
2016-12-28 00:00:00 -09 -0900 Etc/GMT+9
2016-12-28 00:00:00 GAMT -0900 Pacific/Gambier

These are all the timezones it is midnight at 2016-12-28 10:00:00 UTC
2016-12-28 00:00:00 HST -1000 America/Adak
2016-12-28 00:00:00 -10 -1000 Etc/GMT+10
2016-12-28 00:00:00 HST -1000 HST
2016-12-28 00:00:00 HST -1000 Pacific/Honolulu
2016-12-28 00:00:00 CKT -1000 Pacific/Rarotonga
2016-12-28 00:00:00 TAHT -1000 Pacific/Tahiti
2016-12-29 00:00:00 +14 +1400 Etc/GMT-14
2016-12-29 00:00:00 WSDT +1400 Pacific/Apia
2016-12-29 00:00:00 LINT +1400 Pacific/Kiritimati
2016-12-29 00:00:00 +14 +1400 Pacific/Tongatapu

These are all the timezones it is midnight at 2016-12-28 11:00:00 UTC
2016-12-28 00:00:00 -11 -1100 Etc/GMT+11
2016-12-28 00:00:00 NUT -1100 Pacific/Niue
2016-12-28 00:00:00 SST -1100 Pacific/Pago_Pago
2016-12-29 00:00:00 +13 +1300 Etc/GMT-13
2016-12-29 00:00:00 NZDT +1300 Pacific/Auckland
2016-12-29 00:00:00 PHOT +1300 Pacific/Enderbury
2016-12-29 00:00:00 TKT +1300 Pacific/Fakaofo
2016-12-29 00:00:00 FJST +1300 Pacific/Fiji

These are all the timezones it is midnight at 2016-12-28 12:00:00 UTC
2016-12-28 00:00:00 -12 -1200 Etc/GMT+12
2016-12-29 00:00:00 +12 +1200 Asia/Anadyr
2016-12-29 00:00:00 +12 +1200 Asia/Kamchatka
2016-12-29 00:00:00 +12 +1200 Etc/GMT-12
2016-12-29 00:00:00 TVT +1200 Pacific/Funafuti
2016-12-29 00:00:00 MHT +1200 Pacific/Kwajalein
2016-12-29 00:00:00 MHT +1200 Pacific/Majuro
2016-12-29 00:00:00 NRT +1200 Pacific/Nauru
2016-12-29 00:00:00 GILT +1200 Pacific/Tarawa
2016-12-29 00:00:00 WAKT +1200 Pacific/Wake
2016-12-29 00:00:00 WFT +1200 Pacific/Wallis

These are all the timezones it is midnight at 2016-12-28 13:00:00 UTC
2016-12-29 00:00:00 +11 +1100 Antarctica/Casey
2016-12-29 00:00:00 MIST +1100 Antarctica/Macquarie
2016-12-29 00:00:00 +11 +1100 Asia/Magadan
2016-12-29 00:00:00 +11 +1100 Asia/Sakhalin
2016-12-29 00:00:00 +11 +1100 Asia/Srednekolymsk
2016-12-29 00:00:00 AEDT +1100 Australia/Currie
2016-12-29 00:00:00 AEDT +1100 Australia/Hobart
2016-12-29 00:00:00 LHDT +1100 Australia/Lord_Howe
2016-12-29 00:00:00 AEDT +1100 Australia/Melbourne
2016-12-29 00:00:00 AEDT +1100 Australia/Sydney
2016-12-29 00:00:00 +11 +1100 Etc/GMT-11
2016-12-29 00:00:00 BST +1100 Pacific/Bougainville
2016-12-29 00:00:00 VUT +1100 Pacific/Efate
2016-12-29 00:00:00 SBT +1100 Pacific/Guadalcanal
2016-12-29 00:00:00 KOST +1100 Pacific/Kosrae
2016-12-29 00:00:00 NFT +1100 Pacific/Norfolk
2016-12-29 00:00:00 NCT +1100 Pacific/Noumea
2016-12-29 00:00:00 PONT +1100 Pacific/Pohnpei

Notes:

• 2016-12-28 10:00:00 UTC lists both Pacific/Honolulu and Pacific/Tongatapu in its results (and also several others).

• These results list all of the -10/+14 and -11/+13 pairings, as well as a few others, but those few others only involve "sea-faring" timezones such as "Etc/GMT+12".

• If you alter the program to explore 2016-10-16 America/Sao_Paulo is never listed, though it is listed for 2016-12-28 since America/Sao_Paulo has no midnight on 2016-10-16. But you do find America/Bahia listed under 2016-10-16 03:00:00 UTC (offset -0300).

• Changing the program to explore 2016-11-06 shows that America/Havana gets listed both under 2016-11-06 04:00:00 UTC and 2016-11-06 05:00:00 UTC.

• In none of the examples highlighted here has there been a unique timezone where it is midnight for a given UTC timepoint. There probably existed such a timepoint for a timezone with a UTC offset that is not an integral number of hours.

Ah, yes, here are a few:

These are all the timezones it is midnight at 2016-12-27 09:30:00 UTC
2016-12-27 00:00:00 MART -0930 Pacific/Marquesas

These are all the timezones it is midnight at 2016-12-27 10:15:00 UTC
2016-12-28 00:00:00 CHADT +1345 Pacific/Chatham

These are all the timezones it is midnight at 2016-12-27 14:30:00 UTC
2016-12-28 00:00:00 ACST +0930 Australia/Darwin

These are all the timezones it is midnight at 2016-12-27 15:15:00 UTC
2016-12-28 00:00:00 ACWST +0845 Australia/Eucla

These are all the timezones it is midnight at 2016-12-27 15:30:00 UTC
2016-12-28 00:00:00 KST +0830 Asia/Pyongyang

These are all the timezones it is midnight at 2016-12-27 18:15:00 UTC
2016-12-28 00:00:00 NPT +0545 Asia/Kathmandu

These are all the timezones it is midnight at 2016-12-27 19:30:00 UTC
2016-12-28 00:00:00 AFT +0430 Asia/Kabul

These are all the timezones it is midnight at 2016-12-27 20:30:00 UTC
2016-12-28 00:00:00 IRST +0330 Asia/Tehran

These are all the timezones it is midnight at 2016-12-28 03:30:00 UTC
2016-12-28 00:00:00 NST -0330 America/St_Johns

Answer 3

The Answer by Matt Johnson is correct.

• You can get a date if you are certain the moment stored in the database represented the first moment of a day in some time zone somewhere.
• You can guess about the time zone, but you cannot be certain of the original time zone when more than one zone shared an offset-from-UTC at that moment.

java.time

Here is some Java code using the modern java.time classes.

Imagine you take a local date, say 2016-12-28, in a given time zone, say America/New_York, and convert the start of that date into UTC (in this case 2016-12-28T05:00:00Z).

Notice that we let java.time determine the first moment of the day through the LocalDate::atStartOfDay method. Do not assume the day begins at 00:00:00. Anomalies such as Daylight Saving Time mean the day may start at another time such as 01:00:00.

LocalDate localDate = LocalDate.parse( "2016-12-28" ) ;
ZoneId zNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime zdtNewYork = localDate.atStartOfDay( zNewYork ) ;   // First moment of the day in that zone on that date.

Adjust to a UTC value by extract an Instant. An Instant is always in UTC, by definition.

Instant instant = zdtNewYork.toInstant() ;

Store in the database in a column of type akin to SQL-standard TIMESTAMP WITH TIME ZONE.

myPreparedStatement.setObject( … , instant ) ;

get back to the original local date without knowing the time zone?

Retrieve.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

Now experiment with every time zone. FYI, see list of zone names at Wikipedia, though that page may be outdated.

For each zone, adjust our Instant (our UTC moment) into that zone to get a ZonedDateTime object. Some moment, same point on the timeline, but different wall-clock time.

For each ZonedDateTime, extract the date only, without time-of-day and without time zone. Renders a LocalDate object. Ask that LocalDate to determine the first moment of the day (which may or may not occur at 00:00:00) in our time zone under consideration. This produces another ZonedDateTime object.

From that second ZonedDateTime, extract an Instant to adjust back into UTC. Compare this new Instant object with our original Instant. If they are the same, we have a hit. We have identified a time zone when a day began at that moment stored in the database. So the LocalDate we produced above is the same as the date originally stored in inappropriately in our database.

List< ZoneId > hits = new ArrayList<>() ;
LocalDate originalLocalDate = null ;
Set< String > zoneIds = ZoneId.getAvailableZoneIds() ;  // Gets the set of available zone IDs.
for( String zoneId : zoneIds ) {
    ZoneId z = ZoneId.of( zoneId ) ;                        // Get zone with that name.
    ZonedDateTime zdt = instant.atZone( z ) ;
    LocalDate ld = zdt.toLocalDate() ;                      // Extract the date-only value, dropping the time-of-day and dropping the time zone.
    ZonedDateTime startOfDay = ld.atStartOfDay( z ) ;       // Determine first moment of the day on that date in that zone.
    Instant instantOfStartOfDay = startOfDay.toInstant() ;  // Adjust back to UTC.
    boolean hit = instant.equals( instantOfStartOfDay ) ;
    if( hit ) {
        originalLocalDate = ld ;
        hits.add( z ) ;  // Collect this time zone as the zone possibly used originally.
    }
}

See this code run live at IdeOne.com.

When run we see that America/New_York time zone on that date had an offset of five hours behind UTC. You can see from this list, there are many other time zones sharing that same offset of -05:00.

originalLocalDate.toString(): 2016-12-28

hits.toString(): [America/Panama, America/Indiana/Petersburg, America/Eirunepe, Cuba, Etc/GMT+5, Pacific/Easter, America/Fort_Wayne, America/Havana, America/Porto_Acre, US/Michigan, America/Louisville, America/Guayaquil, America/Indiana/Vevay, America/Indiana/Vincennes, America/Indianapolis, America/Iqaluit, America/Kentucky/Louisville, EST5EDT, America/Nassau, America/Jamaica, America/Atikokan, America/Kentucky/Monticello, America/Coral_Harbour, America/Cayman, Chile/EasterIsland, America/Indiana/Indianapolis, America/Thunder_Bay, America/Indiana/Marengo, America/Bogota, SystemV/EST5, US/Eastern, Canada/Eastern, America/Port-au-Prince, America/Nipigon, Brazil/Acre, US/East-Indiana, America/Cancun, America/Lima, America/Rio_Branco, America/Detroit, Jamaica, America/Pangnirtung, America/Montreal, America/Indiana/Winamac, America/New_York, America/Toronto, SystemV/EST5EDT]

Beware that the resulting list of zones may not be distinct. Some zone names are aliases, where one zone has multiple names. For example, the old Asia/Calcutta is now Asia/Kolkata.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.