This truly is one of the wonders of our universe. The power of nature.
If you can, plan to spend the better chunk of the day here so that you can see it at both high and low tide. In between tides, you can walk around the grounds, which are lovely, have lunch in their cafe, which is very good, and go to the various lookouts. There is also an interpretive centre and a decent gift shop. I found it really pleasant to do this. If you can't stay that long, then make sure you go at low tide, so that you can walk on the ocean floor/beach and marvel at the "flowerpots".
Your admission price is good for two consecutive days, so an even better option is to stay overnight in the area and that will ensure that you see the sights at high and low tides.
It also doesn't matter if the weather is sunny or rainy - there is a magic to the place in any weather. You might want to bring bug spray though, just in case.
If you own or manage Hopewell Rocks, register now for free tools to enhance your listing, attract new reviews, and respond to reviewers.