Constrain a postgres timestamp to "End of day"?
For now
While stuck with your unfortunate solution:
CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time)

That's right, AT TIME ZONE two times:

The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times.

The second instance converts the timestamptz back to timestamp at your given time zone. Now you can just check that the time component is whatever you wish.


Cast to time, instead of converting to a string, that's cheaper and more robust.
To get rid of fractional digits you could cast to time(0) instead, but that rounds the values instead of floor in your example. Instead, truncate  with date_trunc(), which is the cheaper method for floor() with positive numbers:
CHECK ((date_trunc('sec', end_time) AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time
        = '23:59:59'::time)
Proper solution
timestamp values have fractional digits and using the time component '23:59:59' as upper limit is an unfortunate decision. Instead, use 00:00 of the next day as exclusive upper border. It is trivial to enforce that with a check constraint.
Next, since you are dealing with multiple time zones, I would suggest to use timestamptz instead of timestamp. Internal storage is the same as with timestamp in the given time zone 'UTC', but input / output handling is different.

The timestamp is shifted to the current time zone automatically on output.
Input timestamps with time zon offset so the values are saved as according UTC times automatically.

Related answer on SO with a lot more details.