postgresql database with time series results with NaT
36 ビュー (過去 30 日間)
古いコメントを表示
This is what I see in pgAdmin: "2022-02-28 16:16:34+00"
This is what the column looks like in matlab: "NaT"
It is strange, because pgAdmin shows the query with the correct dates..
once I import the rows to matlab, most of them become NaT.
0 件のコメント
回答 (3 件)
Peter Perkins
2022 年 3 月 2 日
You need to provide a format in whatever you are using to import:
>> datetime("2022-02-28 16:16:34+00","Format","uuuu-MM-dd HH:mm:ssx","TimeZone","UTC")
ans =
datetime
2022-02-28 16:16:34+00
The fact the only most of them become NaT leads me to think there's a part of the story you are not telling.
0 件のコメント
Pierre-Arnaud Ansel
2023 年 1 月 31 日
I have the same problem. I have a postgres database with a table column whos type is timestamp with time zone. I'm using the native PostgreSQL data source. The timestamps represented have fractional seconds, down to microseconds. They appear fine when I view the table data in pgAdmin. When I do a fetch on this table, any timestamps where the fractional seconds part is not zero appears as NaT in the returned Matlab table.
0 件のコメント
MathWorks Computational Finance Team
2023 年 3 月 13 日
Hi all,
Thank you for reporting this issue. I was able to reproduce this on my end. The issue is due to the timestamp results being returned by the PostgreSQL driver in different formats. For example, I created a table and inserted timestamp values using the follwing SQL command:
>> execute(conn,"INSERT INTO timestampNAT (VAR1) VALUES ('2023-02-06 12:34:15.123')")
>> execute(conn,"INSERT INTO timestampNAT (VAR1) VALUES ('2023-02-06 12:34:15.456')")
>> execute(conn,"INSERT INTO timestampNAT (VAR1) VALUES ('2023-02-06 12:34:16.000')")
When we read the table back in, the PostgreSQL driver returns the following text values:
"2023-02-06 12:34:15.123"
"2023-02-06 12:34:15.456"
"2023-02-06 12:34:16"
Note that the timestamp with all zeros in the sub-second precision is returned by the driver in a different format than the values that do have non-zero sub-second precision. When we convert these string values to datetimes, the datetime function chooses one of these formats, and then sets any values that don't meet that format to NaT.
The workaround I'd suggest for now is to use databaseImportOptions. You can use the options to change the data type from a datetime to a string or char vector. This will allow you to read in the raw text values sent back from the database as seen above. If you require datetime values, you can correct the inconsistent formats once imported and then pass them to the datetime function.
0 件のコメント
参考
カテゴリ
Help Center および File Exchange で Database Toolbox についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!