High Times is the first level in Duke Nukem's 5th episode and the hookah is located near the beginning behind the blue and yellow doors. From the start, grab the nearby blue keycard and go across the river into the little green man coffee shop. Go into the back room with the "medicinal plants" and grab the yellow keycard then open the yellow door. You'll find the hookah here. Use it to get this trophy.
Below is a video showcasing how to get the trophy. It is not mine but is entirely the property of PurrPleeHaze.