How to write database query using sprintf command?
6 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
I have a POSTGRESQL database from which has a column named 'referencetime' which is a timestamp. (e.g. '04/06/2022 12:41:46.5'). I am trying to fetch all data for date 04/06/2022. I am using the following command:
referencetime = char(subjectData.referencetime);
date = referencetime(1:10);
% Select data of same date
dataOnDateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE referencetime = '%s';",date);
But it is not working for me. It works if I hard code it. for example
dataOnDateQuery = "SELECT subjectid FROM live_table WHERE referencetime ILIKE '04/06/2022%';";
But I need the date query to be generalized by '%s' to use in a n algorithm. How can implement both %s and LIKE in the same query? I have attached live_table and the .m file for your reference.
4 commentaires
Jan
le 23 Nov 2022
This line from your question:
dataOnDateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE referencetime = '%s';",date);
does not occur in the attache M-file. Similar lines from the file:
dateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE id = %d;", id);
dataOnDateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE referencetime ILIKE '%s';",date);
It is confusing, if you post different versions of the code, because it is not clear, which one causes which problem.
dataOnDateQuery = "SELECT subjectid FROM live_table WHERE referencetime ILIKE '04/06/2022%';";
Are you sure, that there is a % in the date?
Réponse acceptée
Atanu
le 23 Nov 2022
2 commentaires
Jan
le 24 Nov 2022
This is a wild mixture of string and CHAR vector commands. With one method only, repsectively:
% Strings:
dataOnDateQuery = "SELECT xcoordinates2, ycoordinates2, referencetime " + ...
"FROM live_table WHERE referencetime LIKE '" + thisDate + "%';";
% CHAR vectors:
dataOnDateQuery = sprintf(['SELECT xcoordinates2, ycoordinates2, referencetime ', ...
'FROM live_table WHERE referencetime LIKE ''%s%'';'], thisDate);
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Database Toolbox dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!