How to write database query using sprintf command?

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.

[the cyclist]

It can be a little tricky to get the syntax of these just right. Can you upload the data? Ideally upload subjectData, so that we can run your whole code, but just knowing date should be enough.

As an aside, it is a bad idea to name a variable "date", because that is a MATLAB function. That can come back and bite you. But I don't think that is the issue here.

[Atanu]

Yes, of course. I am attaching both subject data and the .m file here. Thanks for taking a look at this.

[Jan]

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?

[Atanu]

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.

Answer 1

[Atanu]

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);

[Jan]

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);

[Atanu]

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.