Numpy Data Type Demo: datetime64

This demonstrates datetime64 support. Add numpy=True to the connect string to enable the feature.

Setup

Install Anaconda from https://www.continuum.io/downloads
Set the account name, user name and password to the environment variables to SF_ACCOUNT, SF_USER and SF_PASSWORD
Start Jupyter notebook jupyter notebook



In [1]:

    
import os
account=os.getenv('SF_ACCOUNT')
user=os.getenv('SF_USER')
password=os.getenv('SF_PASSWORD')

Set numpy=True in the connect string so that Python Connector fetches and converts TIMESTAMP data to datetime64 numpy data type.



In [2]:

    
from sqlalchemy import create_engine
from snowflake.sqlalchemy import URL
engine = create_engine(URL(
    account=account,
    user=user,
    password=password,
    database='testdb',
    schema='public',
    numpy=True,
    ))



In [3]:

    
engine.execute("""
CREATE OR REPLACE table TS_TABLE(
    c1 timestamp,
    c2 timestamp_ntz,
    c3 timestamp_ltz,
    c4 timestamp_tz)
""")









    Out[3]:





<sqlalchemy.engine.result.ResultProxy at 0x7f5588ccf588>

Snowflake supports TIMESTAMP(9) by default; nanosecond precision is supported.



In [4]:

    
engine.execute("""
INSERT INTO TS_TABLE 
    select
        current_timestamp(),
        current_timestamp()::timestamp_ntz,
        current_timestamp(),
        '2016-06-20T12:34:56.123456789'
""")









    Out[4]:





<sqlalchemy.engine.result.ResultProxy at 0x7f555c61de80>



In [5]:

    
engine.execute("SELECT * FROM TS_TABLE").fetchall()









    Out[5]:





[(numpy.datetime64('2016-06-21T17:52:11.782000000+0000'), numpy.datetime64('2016-06-21T17:52:11.782000000+0000'), numpy.datetime64('2016-06-21T17:52:11.782000000+0000'), numpy.datetime64('2016-06-20T12:34:56.123456789+0000'))]



In [6]:

    
from pandas import DataFrame, Series
import pandas as pd



In [7]:

    
df = pd.read_sql_query("SELECT * FROM TS_TABLE", engine)



In [8]:

    
df.info()









    



<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
Data columns (total 4 columns):
c1    1 non-null datetime64[ns]
c2    1 non-null datetime64[ns]
c3    1 non-null datetime64[ns]
c4    1 non-null datetime64[ns]
dtypes: datetime64[ns](4)
memory usage: 112.0 bytes



In [9]:

    
df









    Out[9]:






  
    
      
      c1
      c2
      c3
      c4
    
  
  
    
      0
      2016-06-21 17:52:11.782
      2016-06-21 17:52:11.782
      2016-06-21 17:52:11.782
      2016-06-20 12:34:56.123456789

Notice c4's value is reserved up to nanoseconds.



In [10]:

    
df2 = pd.read_sql_query("desc ts_table", engine)



In [11]:

    
df2









    Out[11]:






  
    
      
      name
      type
      kind
      null?
      default
      primary key
      unique key
      check
      expression
      comment
    
  
  
    
      0
      C1
      TIMESTAMP_LTZ(9)
      COLUMN
      Y
      None
      N
      N
      None
      None
      None
    
    
      1
      C2
      TIMESTAMP_NTZ(9)
      COLUMN
      Y
      None
      N
      N
      None
      None
      None
    
    
      2
      C3
      TIMESTAMP_LTZ(9)
      COLUMN
      Y
      None
      N
      N
      None
      None
      None
    
    
      3
      C4
      TIMESTAMP_TZ(9)
      COLUMN
      Y
      None
      N
      N
      None
      None
      None



In [12]:

    
df.c1









    Out[12]:





0   2016-06-21 17:52:11.782
Name: c1, dtype: datetime64[ns]



In [13]:

    
df.c4









    Out[13]:





0   2016-06-20 12:34:56.123456789
Name: c4, dtype: datetime64[ns]



In [ ]:

	name	type	kind	null?	default	primary key	unique key	check	expression	comment
0	C1	TIMESTAMP_LTZ(9)	COLUMN	Y	None	N	N	None	None	None
1	C2	TIMESTAMP_NTZ(9)	COLUMN	Y	None	N	N	None	None	None
2	C3	TIMESTAMP_LTZ(9)	COLUMN	Y	None	N	N	None	None	None
3	C4	TIMESTAMP_TZ(9)	COLUMN	Y	None	N	N	None	None	None