Number of seconds elapsed since beginning of day

Database
Enthusiast

Number of seconds elapsed since beginning of day

I have a timestamp with time zone field and I would like to get the number of seconds that have elapsed from the start of that day.  This seems like it should be very trivial but I've searched for hours and tried many things that don't work.  What is a simple method for getting the number of seconds?

2 REPLIES
Senior Apprentice

Re: Number of seconds elapsed since beginning of day

The number of seconds is

extract(hour from ts) * 3600 + extract(minute from ts) * 60 + extract(second from ts)

Dieter

Enthusiast

Re: Number of seconds elapsed since beginning of day

Thanks Dieter.  I was thinking there would be a way to do it in one `cast` instead of three `extract`s, but it probably doesn't matter and this works fine.