postgresql database with time series results with NaT

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.

Answer 1

[Peter Perkins]

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.

Answer 2

[Pierre-Arnaud Ansel]

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.

Answer 3

[MathWorks Computational Finance Team]

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.