Parse time strings with different formats and compare them

Question as stated

You can represent a time without a date using the time data type. To convert a string from a given format into one, you can go through the to_timestamp function and then cast to time:

SELECT to_timestamp('10560684', 'HH24MISSUS')::time;
SELECT to_timestamp('07:57:02.917455', 'HH24:MI:SS.US')::time;

The basic idea is that you parse the time string using to_timestamp. The resulting timestamp will have a default date, and casting to time will remove the date, leaving only the parsed out time portion.

Assumptions:

• Your hours are in 24-hour clock format (13-23 for 1 PM to 11 PM and 00 for midnight). If they are not 24 hour times, then you are missing the AM/PM designation and will need to sort that out.
• The second "SS" you mention in your first pattern is actually a fractional part of seconds. If not, you'll need to adjust the pattern. If you don't care about the fractional seconds, you might consider just leaving the US and the .US off entirely and working only at the seconds level. Note that US interprets 84 to be 0.84 seconds, not actually 84 microseconds (0.000084 seconds).

Ultimately, you will need to either provide much more precise details about the format or figure out the correct format string yourself. Rather than worry about those details, I've tried to exemplify the general mechanism and leave those to you.

Comparison is then trivial. You just use PostgreSQL's operators (<, >, =, etc.):

SELECT to_timestamp('07:57:02.917455', 'HH24:MI:SS.US')::time < to_timestamp('10560684', 'HH24MISSUS')::time;

Other considerations

• Be aware of time zone issues if you are working across them. You'll want to look at timetz (short form of time with time zone) or timestamptz (short form of timestamp with time zone) if you need to deal with time zones. Generally, I would recommend including time zone handling up front in case it becomes a problem later.

• In this case, why not build a complete timestamp? You already have the dates: "public".receipt.rc1_date and "public".employee_log.osemplog_logdate.

  You don't specify the data types, but whatever the forms of those are, it should be possible. For example, if they are actual date objects, then:

  SELECT to_timestamp(to_char("public".receipt.rc1_date, 'YYYY-MM-DD')||' '||"public".receipt.rc1_time, 'YYYY-MM-DD HH24MISSMS');
  

  If they are strings of the form 'YYYY-MM-DD', then:

  SELECT to_timestamp("public".receipt.rc1_date||' '||"public".receipt.rc1_time, 'YYYY-MM-DD HH24MISSMS');
  

  And so on. Now you have a real timestamp, which makes simple great/less than comparison much, much easier.

• In my experience, it's extremely rare that you actually want to test time stamps with fractional second precision for equality. You might want a more tolerant equality check, something like SELECT t1 - t2 < interval '5 seconds', but this is really up to the application.