RE: display to_timestamp in quotas or convert to char ? - Mailing list pgsql-admin

Hi, thank you for reply.
I have already tried to convert timestamp to char like you suggested but that lead to another error.

First, simply execution with NO conversion - to make sure it works like that:
Server2=# select to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
      to_timestamp
------------------------
 2015-05-04 22:13:14+02
(1 row)

Secondly, to_char():
Server2=# select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3'));
ERROR:  function to_char(timestamp with time zone) does not exist
LINE 1: select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And this HINT seems to be odd.

BR,
czezz


Dnia 5 maja 2015 13:46 Albe Laurenz <laurenz.albe@wien.gv.at> napisał(a):

> czezz wrote:
> > I have following table in the DB.
> >
> > Server2=# \d ss21_file
> >               Table "srx.ss21_file"
> >       Column       |          Type          | Modifiers
> > -------------------+------------------------+-----------
> > value             | character varying(30)  |
> > fileindex         | bigint                 |
> > filename          | character varying(80)  |
> > filetime          | character varying(100) |
> > filesizeinrecords | bigint                 |
> > filesizeinbytes   | bigint                 |
> > sourceid          | character varying(300) |
> > Indexes:
> >     "ss21_filetime" UNIQUE, btree (filetime)
> >
> > When executing following query I get following error:
> >
> > Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID
> > FROM ss21_file WHERE filetime < to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
> > ERROR:  operator does not exist: character varying < timestamp with time zone
> > LINE 1: ...ytes, sourceID   FROM ss21_file WHERE filetime < to_times...
> >                                                              ^
> > HINT:  No operator matches the given name and argument type(s). You might need to add explicit type
> > casts.
> >
> > My assumption is that, it is because "filetime" column is "character varying" and in WHERE condition
> > value there is converted to timestamp.
>
> Right.  SQL is a typed language, so every expression has a data type.
>
> "filetime" is "character varying", and the result of "to_timestamp" is
> "timestamp with time zone":
>
> test=> \df to_timestamp
>                                   List of functions
>    Schema   |     Name     |     Result data type     | Argument data types |  Type
> ------------+--------------+--------------------------+---------------------+--------
>  pg_catalog | to_timestamp | timestamp with time zone | double precision    | normal
>  pg_catalog | to_timestamp | timestamp with time zone | text, text          | normal
>
> PostgreSQL cannot find an operator "<" with these argument types, even considering
> implicit casts (see http://www.postgresql.org/docs/current/static/typeconv-func.html).
>
> > When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04
> > 22:13:14.000' (NOTE: single quotas) it works quite good.
>
> That is because there is a comparison operator "text" < "text".
>
> > If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character
> > varying" or display its value in single quotas ?
>
> You could explicitly case the result of "to_timestamp" to "text", like this:
>   ... WHERE filetime < to_char(to_timestamp(...), '...')
>
>
> But there are other solutions:
>
> 1) Define the "filetime" column as type "timestamp with time zone".
>    This is normally the best solution.
>
> 2) Explicitly cast "filetime" to "timestamp with time zone", like this:
>    ... WHERE to_timestamp(filetime, ...) < to_timestamp(...)
>    This has the advantage of checking "filetime" for correctness.
>
> Yours,
> Laurenz Albe
>