Extracting time from timestamp attributes

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

IF HOURS(timestamp) * 60 + MINUTES(timestamp) = HOURS(input timestamp) * 60 + MINUTES (input timestamp) THEN
DISPLAY MESSAGE 'this is a dup!'

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

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

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'

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

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

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.

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.