CODEWITHSUNDEEP
postgresql
tread
tread

Reputation: 11108

How to cast an int of microseconds into a interval field in postgres?

There is this question about how to extract microseconds from an interval field

I want to do the opposite, I want to create an interval from a numeric microseconds. How would I do this?

The reason is I want to take a table of this format

 column_name |        data_type         
-------------+--------------------------
 id          | bigint
 date        | date
 duration    | numeric

and import it into a table like this

 column_name |        data_type         
-------------+--------------------------
 id          | integer
 date        | date
 duration    | interval

Currently I am trying:

select CAST(duration AS interval) from boboon.entries_entry;

which gives me:

ERROR:  cannot cast type numeric to interval
LINE 1: select CAST(duration AS interval) from boboon.entries_entry;

Upvotes: 6

Views: 3745

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28313

you can append the units and then cast to interval

example:

select (123.1234 || ' seconds')::interval

outputs:
00:02:12.1234

valid units are the following (and their plural forms):

microsecond
millisecond
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can do:

select duration * interval '1 microsecond'

This is how you convert any date part to an interval in Postgres. Postgres supports microseconds, as well as more common units.

Upvotes: 6

Related Questions