用1000倍的时间戳

Question

来自雪花的网页界面：

create table example (a TIMESTAMP_NTZ);
insert into example (a) values (current_timestamp);
select * from example;

产量：

2020-09-16 10:28:45.271

现在，从我的终点站：

Python 3.7.9 (tags/v3.7.9:13c94747c7, Aug 17 2020, 18:58:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> import snowflake.connector
>>> import snowflake.connector.pandas_tools
>>> import datetime

>>> connection = snowflake.connector.connect(user="x", account="x.us-east-1.privatelink", authenticator="externalbrowser")
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...

>>> connection.cursor().execute("USE ROLE x")      
<snowflake.connector.cursor.SnowflakeCursor object at 0x0000022D82B73508>
>>> connection.cursor().execute("USE WAREHOUSE x")   
<snowflake.connector.cursor.SnowflakeCursor object at 0x0000022D82DB8A48>
>>> connection.cursor().execute("USE DATABASE x")
<snowflake.connector.cursor.SnowflakeCursor object at 0x0000022D82D7DB08>
>>> connection.cursor().execute("ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE")
<snowflake.connector.cursor.SnowflakeCursor object at 0x0000022D82D73C48>
>>> connection.cursor().execute("USE SCHEMA x")
<snowflake.connector.cursor.SnowflakeCursor object at 0x0000022D82D6EB08>

>>> df = pd.DataFrame({'A': pd.Timestamp('2020-09-16 12:34:56')}, index=[0]) 
>>> success, num_chunks, num_rows, output = snowflake.connector.pandas_tools.write_pandas(conn=connection, df=df, table_name="example")
>>> output
[('nrjuc/file0.txt', 'LOADED', 1, 1, 1, 0, None, None, None, None)]

现在，再次从雪花网络界面：

select * from example;

产量：

2020-09-16 10:28:45.271
52680-03-18 01:13:20.000

我想知道为什么write_pandas插入的第二次约会(52680-03-1801:13:20.000)是不正确的(大约是1,000)。

Answer 1

雪花公司提供的更好的解决方案：

df = pd.DataFrame({'A': [pd.Timestamp('2020-09-16 12:34:56', tz='UTC')]})

使用这种方法，您的dataframe列仍然是一个日期时间，而不是成为一个浮点。

Answer 2

一个解决办法..。改变：

df = pd.DataFrame({'A': pd.Timestamp('2020-09-16 12:34:56')}, index=[0]) 

至

df = pd.DataFrame({'A': pd.Timestamp('2020-09-16 12:34:56').timestamp()}, index=[0]) 

.timestamp()方法将日期时间转换为浮点数，雪花似乎将得到的浮点数解释为该时代过去的秒数，并将其转换为雪花时间戳。

Answer 3

在我的示例中，python中的日期列看起来很好，但在雪花中却以毫秒的形式出现，我通过将np.datetime64转换为object来解决这个问题：

output['SUBMODEL_VALUE_DATE'] =output['SUBMODEL_VALUE_DATE'].dt.strftime("%Y-%m-%d")