Postgresql: Convert a date string '2016-01-01 00:00:00' to a datetime with specific timezone
<p>I'm in a tricky situation. A client's database timezone was configured for America/Chicago instead of UTC.</p>

<p>From the app, we ask customers to enter useful dates, and sadly those dates are stored 'as-is', so if they entered '2001-01-01 00:00:00' in the text input, that same value will be stored in the DB, and we are ignoring the customer's timezone. We save that info separately.</p>

<p>The table column is of type TIMEZONETZ. So Postgresql will add the America/Chicago timezone offset at the end: Eg '2001-01-01 00:00:00-02'. 
Naturally, most of the customers are not in Chicago.</p>

<p>The difficult part, is that, even knowing the customer's timezone, it's really hard to run calculations on the DB given that the datetime was not correctly pre-processed before storing it into the DB.</p>

<p>My attempted solution, is finding a way to extract the datetime string from the column value, and re-convert it to a date with the right timezone. Eg (pseudo-code):</p>

<pre><code>// This is psuedo code
SELECT DATETIME((SELECT date_string(mycolumn) FROM mytable),  
TIMEZONE('America/Managua'));

Which would be equivalent in PHP:
$customerInput = '2016-01-01 00:00:00';
$format = 'Y-m-d H:i:s';
$wrongDateStoredInDb = DateTime::createFromFormat($format, $customerInput, new DateTimezone('America/Chicago'));

// In order to fix that date, I'd extract the dateString and create a new DateTime but passing the correct timezone info.

$customerTimezone = new Timezone('America/Bogota');
$customerInput = $wrongDateStoredInDb-&gt;format($format); // Assuming we didn't have it already.
$actualDateTime = DateTime::createFromFormat($format, $customerInput, $customerTimezone);
</code></pre>

<p>With that kind of information, I'd be able to run calculations on date ranges, with the correct values, eg:</p>

<pre><code>// Pseudo-code
SELECT * FROM myTable WHERE fix_date_time(columnWithInvalidDate, `correctTimezone`)::timestamp &gt; `sometimestamp`;
</code></pre>

<p>I've read the Postgresql docs, and I've tried everything I could, but nothing seems to work.</p>

<p>Any suggestion is more than welcomed!</p>