Search code examples
postgresqlpostgresql-9.6

Can we alter type of epoc time stored as text column in postgresql

select to_timestamp(created)::timestamp from users limit 1;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

i am trying to convert epoc time column stored as string in postgresql to timestamp.But getting above error.Any suggestion.

Solution

  • There are two versions of the to_timestamp function, one which takes a float argument (expected to be a unix epoch offset) and the other which takes two text arguments, where the first argument is a text string, and the second maps the formatting of the first text argument.

    In your case, you are storing the epoch offsets as text, but you basically want the first version of the function. You should be able to do something like:

    select to_timestamp(created::float) from users limit 1;