CODEWITHSUNDEEP
databasepostgresql
Angel
Angel

Reputation: 357

How can I get timestamp in minutes using PostgreSQL

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

Upvotes: 8

Views: 26626

Answers (1)

fog
fog

Reputation: 3391

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.)

Upvotes: 27

Related Questions