- https://in.mathworks.com/matlabcentral/answers/1660440-postgresql-database-with-time-series-results-with-nat
- https://in.mathworks.com/help/database/ug/databaseimportoptions.html
Data Import from PostgreSQL produces NaT's
5 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
I need to fetch data from a Postgres database. One of the columns in the table I am querying has type "timestamp with time zone". The values in this column can have fractional seconds down to microseconds. I am connecting to the database server using the native postgresql data source. I do a simple fetch from this table. Values in the timestamp column which are a whole number of seconds (e.g. 31-Jan-2023 17:02:00.000000) appear correctly in the returned matlab table object, but where the timestamp has a non-zero fractional component (e.g. 31-Jan-2023 17:02:00.001234), the imported value is "NaT".
If I inspect the original table in pgAdmin, all the timestamp values are correctly displayed. How can I ensure that all the timestamp values are correctly imported?
My Postgres version is 9.3.25
Here's what I'm doing (exact database names, passwords etc. obfuscated).
vendor = 'PostgreSQL';
dbname = 'target_database';
opts = databaseConnectionOptions('native',vendor);
opts = setoptions(opts, ...
'DataSourceName', 'PostgreSQLDataSource', ...
'DatabaseName', dbname, ...
'Server', 'localhost', ...
'PortNumber', 5432);
saveAsDataSource(opts);
%% Make connection to database
conn = postgresql('PostgreSQLDataSource','myuser','mypassword');
%Set query to execute on the database
query = 'select * from target_index_table';
%% Execute query and fetch results
data = fetch(conn,query);
%% Close connection to database
close(conn)
%% Clear variables
clear conn query
0 commentaires
Réponses (1)
HimeshNayak
le 17 Mar 2023
Hi Paul,
As per my understanding, when you are importing data from PostgreSQL database, the “timestamp” values with a non-zero fractional component are getting converted to “NaT”.
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. If you require datetime values, you can correct the inconsistent formats once imported and then pass them to the datetime function.
For more information, refer the following links:
Regards
HimeshNayak
2 commentaires
Voir également
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!