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.
Extracting time from timestamp attributes
Re: Extracting time from timestamp attributes
IF HOURS(timestamp) * 60 + MINUTES(timestamp) = HOURS(input timestamp) * 60 + MINUTES (input timestamp) THEN
DISPLAY MESSAGE 'this is a dup!'
DISPLAY MESSAGE 'this is a dup!'
Re: Extracting time from timestamp attributes
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?
Re: Extracting time from timestamp attributes
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'
IF COUNT YourBO WHERE (HOUR(YourBO.datetimefield) * 60 + MINUTES (YourBO.datetimefield) = HOUR(inputdatetimefield) * 60 + MINUTES(inputDateTimeField) > 0 THEN REPORT ERROR 'dup time'
Re: Extracting time from timestamp attributes
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'
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
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
Re: Extracting time from timestamp attributes
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.
-
- Posts: 1462
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Extracting time from timestamp attributes
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.
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.
-
- Posts: 7526
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Extracting time from timestamp attributes
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
Re: Extracting time from timestamp attributes
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.