title: Convert formatted date string to TIMESTAMP in Hive, taking into account dailight saving time
body: <p>I want to convert dates in the following format<code>'03/21/2006 10:00:00 PM'</code> to the TIMESTAMP data type in Hive. The strings are from Chicago's time zone, meaning that daylight saving time applies. I do <strong>not</strong> want to convert the time to UTC in the process.</p>
  
  <p>I have tried the following but <code>unix_timestamp()</code> does not understand the time zone <code>America/Chicago</code>.</p>
  
  <pre><code>SELECT cast(
     from_unixtime(
       unix_timestamp('03/21/2006 10:00:00 PM America/Chicago', 'MM/dd/yyyy hh:mm:ss a zzzz')
     ) AS TIMESTAMP
  );
  </code></pre>
  
  <p>I could use <code>CDT</code> or <code>CST</code>as the time zone depending on whether the date lies within dailight saving time or not but this would get messy as the date for time change varies from year to year.</p>
  
  <p>Is there a better way to do this?</p>
  
  <p>I am using Hive 1.1.0.</p>
  
  <p>Thank you for your time.</p>
  
  <hr>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have been able to achieve my goal using the below code.
  The solution is neither concise nor elegant but it does the job for now.
  A UDF would have achieved the same job in a more modular manner but I do not yet have any experience with writing Hive UDFs.</p>
  
  <pre><code>,from_utc_timestamp(to_utc_timestamp(concat(
          regexp_extract(date, '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 3) -- year
          ,'-'
          ,regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 1) -- month
          ,'-'
          ,regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 2) -- day
          ,' '
          ,CASE  -- hour
              WHEN regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 6) = 'AM'   
                  AND regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 4) = '12' 
                  THEN '00'
              WHEN regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 6) = 'AM'   
                  AND regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 4) &lt;&gt; '12' 
                  THEN regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 4) 
              WHEN regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 6) = 'PM' 
                  AND regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 4) = '12' 
                  THEN '12'
              WHEN regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 6) = 'PM' 
                  AND regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 4) &lt;&gt; '12'
                  THEN cast(regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 4) AS TINYINT) + 12
              ELSE NULL -- should never get here
          END
          ,regexp_extract(date,  '(\\d{2})/(\\d{2})/(\\d{4}) (\\d{2})(:\\d{2}:\\d{2}) ([A|P]M)', 5) -- rest of time
          ) 
       , 'America/Chicago')
       , 'America/Chicago') AS cast_date
  </code></pre>