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
2
Answers
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:
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)This is possible with some date arithmetic:
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.