Extracting time from timestamp attributes

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
lueu
Posts: 89
Joined: Tue Mar 07, 2023 11:49 pm

Extracting time from timestamp attributes

Post by lueu »

I would like to get the time-part from several timestamp attributes. The goal is to compare the time in some columns in an existing row with the time in a row the user wants to insert, to prevent insert of a duplicate row. Since the user will only provide time, and no date, for these columns (Monday 08.00-20.00 for example), it is impossible to compare since AwareIM sets the date to someday 1970.

Can I somehow use a calculated column and a rule to extract the time and then use this new column for comparing? If so, does anyone have the knowledge and time to give an example of how this could be done in AwareIM? Search both documentation and forum without finding what I am looking for.
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Extracting time from timestamp attributes

Post by BLOMASKY »

IF HOURS(timestamp) * 60 + MINUTES(timestamp) = HOURS(input timestamp) * 60 + MINUTES (input timestamp) THEN
DISPLAY MESSAGE 'this is a dup!'
lueu
Posts: 89
Joined: Tue Mar 07, 2023 11:49 pm

Re: Extracting time from timestamp attributes

Post by lueu »

Thanks! Have further questions, though. How do I get the time value from awareIM attribute to compare with my input value, since I can't do it directly on the existing attributes which include dates?
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Extracting time from timestamp attributes

Post by BLOMASKY »

Perhaps this is what you want?

IF COUNT YourBO WHERE (HOUR(YourBO.datetimefield) * 60 + MINUTES (YourBO.datetimefield) = HOUR(inputdatetimefield) * 60 + MINUTES(inputDateTimeField) > 0 THEN REPORT ERROR 'dup time'
rocketman
Posts: 1239
Joined: Fri Jan 02, 2009 11:22 pm
Location: Preston UK
Contact:

Re: Extracting time from timestamp attributes

Post by rocketman »

I've often thought how much easier life would be if only Vladimir would give us a TIME_PART function to go alongside the DATE_PART, but seriously if you are comparing timestamps that all default to the AIM arbitrary date of (I think) 1970 - won't a simple if stored.timestamp = input.timestamp do? so it would be something like

IF EXISTS(BO WHERE BO.timestamp = input.timestamp THEN REPORT ERROR 'You have a duplicate'
Rocketman

V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
lueu
Posts: 89
Joined: Tue Mar 07, 2023 11:49 pm

Re: Extracting time from timestamp attributes

Post by lueu »

When the timestamp in Aware has a date 1970, and the input timestamp has todays date, they would never be the same. I am now trying to use a substring to extract the time to compare - the substring works, but still not worked out the compare part. Tried IF EXISTS, but for some reason I've still to figure out, I can't seem to get my error message to display even when I know the time part is the same.
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Extracting time from timestamp attributes

Post by PointsWell »

The date part could be set to whatever you want it to be.

This thread is somewhat confusing, to me at least. It might be more helpful to provide more context to the problem by describing the business problem rather than just the technology problem.

If the user isn't choosing a date then does the attribute need to be a timestamp rather than just a time field, which could be combined with a date field to get you to your timestamp via a business rule. Without knowing what you are doing it is very hard to provide other potential options.
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: Extracting time from timestamp attributes

Post by aware_support »

I am not sure I understand everything in this thread, but check out at the AS_STRING function, which can format a date according to the specified format - for example AS_STRING (Object.TimestampAttr, "HH:mm") will convert a timestamp to a time string.
Aware IM Support Team
lueu
Posts: 89
Joined: Tue Mar 07, 2023 11:49 pm

Re: Extracting time from timestamp attributes

Post by lueu »

Thanks, support! Will try that next time, we worked around the problem this time by doing something different so we didn't need to do the extracting.
Post Reply