Timestamp and substring with regular expression

Question

I am using postgresql and I have the following table

          Table "public.test"
 Column |           Type           | Modifiers 
--------+--------------------------+-----------
 name   | text                     | 
 time   | timestamp with time zone | 


 name  |          time          
-------+------------------------
 ticka | 2016-07-08 21:22:58+00
(1 row)

When I do the command:

select substring(name from '.*') from test;

I get a result, but when I do it for the time column I get:

Error: LINE 1: select substring(time from '.*' ) from test;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Why is this? How can I solve it?

Answer 1

Ok, because the function is called substring, I think it only works for the string data type but not for the timestamp data type

To solve this I use the regular expression on the timestamp by converting the timestamp into a text first using the to_char function and then I use the substring function as follows:

select substring(to_char(time,'YYYY-MM-DD HH:MM:SS') from '.*') from test;