How can I get timestamp in minutes using PostgreSQL

Question

When I send a form on my web, the insert query saves the current date on my DB using the function now().

Now, I'm trying to get this column in minute format to calculate other thinks that I need, but I don't know how to do that.

For example, I have this:

"2013-05-08 08:30:00"

And I want this (now 8.50):

"20" <- In minutes

Thanks

In the first line of the example the minutes are "30". In the second, "50". In the third "20". Can you explain a little better what you want? — fog
– fog, Commented May 8, 2013 at 6:55
Ok, but what do you want if now is 9:40? A result of 70 or 10? — fog
– fog, Commented May 8, 2013 at 6:59
Yes, 70. I need the difference in minutes between now and the date in DB. — Angel
– Angel, Commented May 8, 2013 at 7:02

fog · Accepted Answer · 2013-05-08 07:07:45Z

27

OK, let's suppose you have a table with a timestamp:

CREATE TABLE ex (t timestamp);
INSERT INTO ex VALUES ('2013-05-08 8:30'::timestamp);

And you want the difference in minutes between the column t and now(). You can get that using the extract function:

SELECT extract(epoch from (now() - ex.t)) / 60 FROM ex;

epoch is the number of seconds from the "epoch" for date and timestamp types, but is't just the number of seconds in the interval for interval types. By dividing it by 60 you get what you want (if you want an integer number of minutes just trunc it.)

answered May 8, 2013 at 7:07

fog

3,4411 gold badge27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Angel Over a year ago

OMG. This community is amazing. YOU are amazing. Thanks so much, it works perfect!!

Collectives™ on Stack Overflow

How can I get timestamp in minutes using PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related