title: Error: column is of type timestamp without time zone but expression is of type double precision
body: <p>The following code...</p>
  
  <pre><code>import time
  import numpy as np
  import pyodbc
  import datetime
  import win32com.client
  import pythoncom
  import re
  from sqlalchemy import create_engine, event
  import pandas as pd
  import runpy
  import codecs
  import collections
  from multiprocessing import Process, Queue
  from datetime import datetime, timezone, timedelta
  import threading
  import os
  import csv
  import warnings
  import xlsxwriter
  from threading import Timer
  import psycopg2
  
  warnings.filterwarnings("ignore")
  import time
  import win32com.client
  import threading
  from datetime import datetime, timezone, timedelta
  import numpy as np
  import pyodbc
  import datetime
  import pandas as pd
  import runpy
  import codecs
  import collections
  from multiprocessing import Process, Queue
  import threading
  from datetime import timedelta
  import warnings
  from win32com import client
  from datetime import datetime
  import os
  #from docx import Document
  #from docx.shared import Inches, Pt, Mm
  import time
  import numpy as np
  import pyodbc
  import datetime
  import pandas as pd
  import runpy
  import codecs
  import collections
  from multiprocessing import Process, Queue
  import threading
  from datetime import timedelta
  from datetime import datetime
  import os
  import time
  import glob
  import time
  import time
  from datetime import datetime, timezone, timedelta
  import time
  import win32com.client
  import threading
  from datetime import datetime, timezone, timedelta
  import numpy as np
  import pyodbc
  import datetime
  import pandas as pd
  import runpy
  import codecs
  import collections
  from multiprocessing import Process, Queue
  import threading
  from datetime import timedelta
  import warnings
  from win32com import client
  from datetime import datetime
  import os
  #from docx import Document
  #from docx.shared import Inches, Pt, Mm
  import time
  import numpy as np
  import pyodbc
  import datetime
  import pandas as pd
  import runpy
  import codecs
  import collections
  from multiprocessing import Process, Queue
  import threading
  from datetime import timedelta
  import warnings
  from win32com import client
  from datetime import datetime
  import os
  import time
  import glob
  import time
  import time
  from datetime import datetime, timezone, timedelta
  
  warnings.filterwarnings("ignore")
  kronos_df = pd.read_excel(r"\\teslamotors.com\us\Public\stalamakki\ExcelFiles\KronosDataHourlyRefresh.xls")
  
  kronos_df.fillna('')
  clockRecords = kronos_df.to_dict('records')
  
  sqlUpsert = """
                          INSERT INTO "daily_performance_metrics"."employee_kronos_data_2"
                          VALUES (%s,%s,%s,%s,%s)
  """
  #                        VALUES (%s,to_timestamp(%s, 'YY-MM-DD HH24:MI'),COALESCE(to_timestamp(NULLIF(%s, '01/01/01 00:00'),'MM/DD/YY hh24:mi')),%s,%s)
  
  #sqlDelete = """
  #        DELETE FROM "daily_performance_metrics"."employee_kronos_data" WHERE CustomerName='Alfreds Futterkiste';
  
  postgres_conn = psycopg2.connect("host=sjc04p1scadb02.teslamotors.com dbname=service_warehouse_metrics user=service_warehouse_rw port=5432 password=gvjY96LcnWn2B3+obVjFsLG5erMy/4JNxgN00Lnq2n0=")
  postgres_cursor = postgres_conn.cursor()
  for record in clockRecords:
      if record['ShiftEnd'] == '':
         record['ShiftEnd'] = None
      if record['ShiftStart'] == '':
         record['ShiftStart'] = None
      postgres_cursor.execute(sqlUpsert,list(record.values()))
  postgres_conn.commit()
  postgres_cursor.close()
  postgres_conn.close()
  </code></pre>
  
  <p>...generates this error message when it tries to write what I assume is the first record with a null value...</p>
  
  <pre><code>---------------------------------------------------------------------------
  DatatypeMismatch                          Traceback (most recent call last)
  &lt;ipython-input-73-2ef7c8c3820c&gt; in &lt;module&gt;()
       15     if record['ShiftStart'] == 'NaN':
       16        record['ShiftStart'] = None
  ---&gt; 17 postgres_cursor.execute(sqlUpsert,list(record.values()))
       18 postgres_conn.commit()
       19 postgres_cursor.close()
  
  DatatypeMismatch: column "shift_end" is of type timestamp without time zone but expression is of type double precision
  LINE 3: ...              VALUES ('zvolkert','10/02/19 13:13','NaN'::flo...
                                                               ^
  HINT:  You will need to rewrite or cast the expression.
  </code></pre>
  
  <p>To deal with the Nulls, I've tried this syntax for the INSERT statement...</p>
  
  <pre><code>INSERT INTO "daily_performance_metrics"."employee_kronos_data_2"
  VALUES (%s,to_timestamp(%s, 'YY-MM-DD HH24:MI'),COALESCE(to_timestamp(NULLIF(%s, '01/01/01 00:00'),'MM/DD/YY hh24:mi')),%s,%s)
  </code></pre>
  
  <p>...which generates this error message...</p>
  
  <pre><code>InvalidTextRepresentation: invalid input syntax for type double precision: "01/01/01 00:00"
  LINE 3: ...4:MI'),COALESCE(to_timestamp(NULLIF('NaN'::float, '01/01/01 ...
  </code></pre>
  
  <p>I'm assuming this is a very simple syntax mistake. Would really appreciate if someone could tell me the correct syntax for getting these strings and null values into the timestamp fields.</p>
  
  <p>I'm writing to this table...</p>
  
  <pre><code>CREATE TABLE daily_performance_metrics.employee_kronos_data_5 (
      file_number TEXT
      ,shift_start TIMESTAMP
      ,shift_end TIMESTAMP
      ,job_category TEXT
      ,job_name TEXT
      )
  </code></pre>
  
  <p>Here's the file that i'm trying to copy to the database: </p>
  
  <p><a href="https://onedrive.live.com/embed?cid=0A882D15600C2281&amp;resid=A882D15600C2281%21329&amp;authkey=ABG_DRZjBwgQXao" rel="nofollow noreferrer">ClockInOutRecords.xlsx</a></p>