Date comparison in query using CC

General Q&A
1

I created a function to pull data for the current day and create a pdf report, worked great. I want to make this a scheduled CC task that I would run every night. I know in CC the date is different and I convert to US time before comparing, but I never get a result. I have tried = and contains both. I have tried new Day() like I did in JS. timestamp is defined as datetime. Any ideas on this?

JS (returns correct count of orders)
var today = new Day();

var sjg = DB.storeroom_order_items.where('ship_to contains ? and timestamp = ?', '72156', today).orderBy('ship_to','username', '-timestamp').toArray();

CC (returns no orders found)
// Get orders for Today.
var usaTime = new Date().toLocaleString(“en-US”, {timeZone: “America/New_York”});
var today = (new Date(usaTime)).toISOString();
console.log('today: ’ + today);

var sjg = await DB.storeroom_order_items.where('ship_to contains ? and timestamp = ?', '72156', today).orderBy('ship_to', 'username', '-timestamp').toArray();
2

@fred-michael you might want to use a different pattern to get the same result. Add a new field generate_pdf_report to storeroom_order_items. Then you can run your scheduled CC task with,

var sjg = await DB.storeroom_order_items.where('ship_to contains ? and generate_pdf_report = ?', '72156', null).orderBy('ship_to', 'username', '-timestamp').toArray();

Then when you store your report you also set your new field to true.

3

@fred-michael if you do want to use times you will have to change your where to something like the following:

var sjg = await DB.storeroom_order_items.where('ship_to contains ? and timestamp <=  ? and timestamp >= ?', '72156', today, today_min_24_hour).orderBy('ship_to', 'username', '-timestamp').toArray();

Where today is of type Date.

let today = new Date();
let today_min_24_hour = new Date(today.valueOf() - (24 * 60 * 60 * 1000));