How do I include constraints in data explorer?

I am new to using the Database Explorer app. I am currently pulling data from an external database into Matlab. Using the Database Browser and the Data Preview, I have no problem building the table I need. I understand how to use the SQL criteria panel to input different constraints. My problem is that I have a date column and I only want to include entries from the last year in the table (today-365). The date column is in the format of yyyy-mm-dd 00:00:00.0. Can I input a formula into the criteria panel?

Answer 1

[Daniel Burke]

If you only need to import this data once, and assuming your entire date column is formatted in the same way, even if the date is stored as text you should be able to do a comparison with another string that will yield correct results. I.e.

t = ‘2017-08-07 00:00:00.0.’ >  ‘2016-08-07 00:00:00.0.’ %True
f = ‘2015-05-17 00:00:00.0.’ >  ‘2016-08-07 00:00:00.0.’ %False

So from the Database Explore when you select your table you want to import in the left hand side and open up the ‘Where’ menu in the criteria section you should be able to select the date column, use the > operator, and input ‘2016-08-07 00:00:00.0.’ as the value and, provided your table doesn’t have values from the future that should select only values from the last year, which you can then hit ‘Close where’ and import your data to MATLAB. You can also add more constraints in the where tab and set an upper bound for the date if you desire.

However, if you want to use a formula to form your ‘where’ query then rather than using the database explore app you will have to interface with the database from the command line. So if you wanted to take today’s date for instant and subtract a year, you would have to use the datetime object or some method to get the date a year ago and then insert that string into a ‘where’ query you write yourself, in which you can do the same comparisons between strings that I mentioned above. If that is something you want to do you can read more about interfacing with a database from the command line here:

https://www.mathworks.com/help/database/ug/database.html

However, if this is a one-time import doing the first method through the Database Explore might be a lot easier.

Link to datetime object as well:

https://www.mathworks.com/help/matlab/ref/datetime.html

[Chris]

Thanks for your input! This data will be pulled less frequently than once per month, on varying days. It would save time to simply have the right data pulled by the .m file. I currently have the script set up to set x=datetime('now') and would like to have a Where line something like "WHERE ShopCalendar.CalendarDate between (x-365) and (x-1)". I am just not sure how to properly write the Where line.

[Daniel Burke]

Doing the function processing 'x-365' and 'x-1' would be difficult to do in SQL, especially when interfacing from MATLAB. So I think it would be easier to do the calculations for that in MATLAB itself and use those to build a WHERE query that you can send to the SQL database.

In the original post you said your format was: yyyy-mm-dd 00:00:00.0

You can get the current date and last years date from MATLAB into a string that matches this format using this code

dt = datetime('now')
ds = datestr(dt,31)
ds = [ds '.0']
dt_lastyear = dt + calyears(-1)
ds_lastyear = datestr(dt_lastyear,31)
ds_lastyear = [ds_lastyear '.0']

This gives you

 ds ='2017-08-08 13:56:21.0'
 ds_lastyear ='2016-08-08 13:56:21.0'

(Note: the datestr does not allow you to specify the tenths place in milliseconds so I just appended '.0' to the end of the string to match the format and assuming the milliseconds wont make a difference)

You can then make a where query by formatting the string like so

whereQuery = ['WHERE ShopCalendar.CalendarDate > ' ds_lastyear ' && ShopCalendar.CalendarDate < ' ds]

If you append this where query to the end of you SELECT statement this should get you the data from you table within the last year based on your format.