CODEWITHSUNDEEP
postgresqltype-conversiondata-conversion
Jennifer
Jennifer

Reputation: 49

How to extract the integer value of a time formatted as "MI:SS"

I would like to import an Excel file in which times are formatted as "00:00" (minutes:seconds). The corresponding field in the database is designated as an integer.

Which function can I use to enter the value and convert it to the corresponding integer representation? For example: "02:39" should return "159".

I attempted to google as well as look at various sources like: https://www.postgresql.org/docs/current/functions-datetime.html. I couldn't find the function I needed

Upvotes: 0

Views: 47

Answers (2)

hobgoblin
hobgoblin

Reputation: 337

This is possible with some date arithmetic:

select extract('epoch' from interval '00:02:39');

Extracting 'epoch' from a time or interval value gives the number of seconds in the interval. In your case (mm:ss), you need to prepend '00:' or the literal is read as hh:mm. The hh, mm, and ss fields are limited to 0-23 and 0-59 resp. (for intervals, the hour field can be greater than 23).

Features depend on the Postgresql version, I have used 16. Here is a fiddle with a slightly more elaborate example.

Upvotes: 2

Belayer
Belayer

Reputation: 14934

There is no native function for the conversion you're looking for. The solution therefore is a user defined function. The function needs to take your formatted string, parse it into its distinct parts, and finally compute and return the number of seconds. Fortunately, this is an easy task:

create or replace function convert_miss_to_integer( miss_in text)
  returns integer
 language sql
as $$
    with time_miss(mi,ss) as 
         (select substring(miss_in,1,position(':' in miss_in)-1)::integer
               , substring(miss_in,position(':' in miss_in)+1)::integer
         )
    select 60*mi+ss
      from time_miss;
$$;

The time_miss cte parses the input string into its desired minuets (mi) and seconds (ss) using the substring and position string functions; the main select then computes the total seconds. As a SQL function the results are returned. (see demo)

Upvotes: 2

Related Questions