PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search