How to write database query using sprintf command?

10 views (last 30 days)
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 Comments
Struggling in MATLAB
Struggling in MATLAB on 23 Nov 2022
Edited: Struggling in MATLAB on 23 Nov 2022
Hi Jan, sorry for not explaining my question well. I have not attached the 'live_table' in this question. The 'dateQuery' query merely fetches the referencetime corresponding to the id. The date part from the referencetime in turn is used in 'dataOnDateQuery' to fetch all data on that date. Let me attach the partial 'live_table' here.

Sign in to comment.

Accepted Answer

Struggling in MATLAB
Struggling in MATLAB on 23 Nov 2022
I was finally able to solve this by separating '%s' and 'LIKE' in the query string. Here is the code attached.
referencetime = char(subjectData.referencetime);
thisDate = referencetime(1:10);
% Select data of same date
dataOnDateQuery = strcat("SELECT xcoordinates2, ycoordinates2, referencetime " + ...
"FROM live_table WHERE referencetime LIKE '",sprintf('%s',thisDate), "%';");
dataOnDate = fetch(conn,dataOnDateQuery);
  2 Comments
Struggling in MATLAB
Struggling in MATLAB on 2 Dec 2022
Thanks for your response. I tried both of them. The 1st method works, but the 2nd did not. I getting an error.
JDBC Driver Error: Unterminated string literal started at position 92 in SQL SELECT
xcoordinates2, ycoordinates2, referencetime FROM live_table WHERE referencetime LIKE
'04/06/2022. Expected char
But, it's okay. I just needed one method.

Sign in to comment.

More Answers (0)

Products


Release

R2022b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!