Date logic madness

[Egil_Helland]

I am struggling to parse simple dates, so I hope someone here can help save me from insanity

The case is this:

I want to fetch all records with datetime column updated = today (never mind the timestamp, only the datestamp), to do something with these records in the database.

In other SQL tools I would use a WHERE statement like: updated=TODAY or updated>=TODAY AND updated<TOMORROW.

Today is the 18th of February 2024, 2024-02-18 in ISO format. I have two records that are updated today.

In console, using today and tomorrows date, updated>='2024-02-18' AND updated<'2024-02-19' does not work. Not what I expected. But if I do updated>='2024-02-17' AND updated<'2024-02-18', I find my records. Same results using locale formatting of the dates instead of ISO.

• I confused myself here to no end - the records I was looking at was actually not updated today, but yesterday. So the paragraph above is then, of course, correct!

I then tried with the MySQL formulas, using: updated>=Date(Now()), but that does not work. If I reversed the greater than sign, I get results, but that clearly means that the Date() function does not work according to MySQL documentation. ADD_DATE or SUB_DATE does not seem to work. I cannot find out what is actually supported in the Backendless documentation here. Is there an up to date doc on this somewhere?

Then I tried to add in actual dates using the date logic functions in Codeless instead. But these functions are also poorly documented (or at least I am not able to find anything better than this: Date - Backendless Codeless Development Guide). So I am a bit at loss as to how to use these as well.

These are my findings from testing the functions related to parsing and getting dates:

• Date Now
  This is my starting point, gives a unix timestamp in milliseconds
• Date from timestamp
  This function does not return a date as the function says, but rather returns an ISO 8601 formatted string of the provided unix timestamp, e.g. 2024-02-18T21:10:27.998Z
• get xxx for date
  The UTCMonth returned is 1, not 2? This seems like a bug to me? Also, if it should be in UTC format, it should have 2 digits, e.g. 02.
  
  
• convert to xxxx
  I tried them all, but only localeDateString outputs the date only:
  
  

As far as I can understand, I need to either:

• create a new timestamp for today at 00:00:00, and one for tomorrow with timestamp 00:00:00, and find records in between these two, or
• subtract 1 day from the date as datestamp 1, and compare againt todays datestamp, and find records between these two

Regardless of approach, I don’t see an obvious and easy way to parse these dates unless I go by way of string manipulation and remove the time portion of the string, and puts a 00:00:00 element back in. That seems a ridiculous way of doing date/time manipulation, so now I am pretty much back to square 1 - unsure of what to do.

All I want is to be able to find all records with a timestamp where the date portion of that timestamp equals to today.

It is probably easy, but I am still here scratching my head. Any help appreciated!

[Egil_Helland]

This is what I have ended up with for now, to get the dates of today, yesterday and tomorrow with 00:00:00 timestamps. Better solutions than this appreciated greatly

[stanislaw.grin]

Hello @Egil_Helland,

sorry for the delayed response!

Here is what I can suggest to you:

1. Create a custom function Get Formatted Code which will accept a date as an argument
2. Inside this custom function create a Custom Code block called getFormattedDate
3. Put the following code inside it:

const ensureTwoDigitFormat = value => value < 10 ? '0' + value : value

// Create a new Date object with the desired date
const date = new Date(dateValue)

// Extract the date components
const month = ensureTwoDigitFormat(date.getMonth() + 1) // Months are zero-based, so we add 1
const day = ensureTwoDigitFormat(date.getDate())
const year = date.getFullYear()

// Create the formatted date string
const formattedDate = `'${ month }/${ day }/${ year }'`

return formattedDate

(feel free to remove comments or modify whatever you want)

Here’s my custom function that uses this custom code block:

This function will return the date in the correct format which you can use in the where-clause the following way:

Hope it will help you.

Regards,
Stanislaw