Subject: =?UTF-8?Q?RE:_display_to=5Ftimestamp?=
 =?UTF-8?Q?_in_quotas_or_convert_to_char_=3F?=
From: =?UTF-8?Q?czezz?= <czezz@o2.pl>
To: =?UTF-8?Q?Albe_Laurenz?=<laurenz.albe@wien.gv.at>
Cc: pgsql-admin@postgresql.org
In-Reply-To: 
 <A737B7A37273E048B164557ADEF4A58B365FC46A@ntex2010a.host.magwien.gv.at>
References: <22c93a48.7dc1250c.5548946d.f29b0@o2.pl>
 <A737B7A37273E048B164557ADEF4A58B365FC46A@ntex2010a.host.magwien.gv.at>
Mime-Version: 1.0
Message-ID: <70aed348.7ad50e54.5548c47d.1e1de@o2.pl>
Date: Tue, 05 May 2015 15:24:13 +0200
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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

First, simply execution with NO conversion - to make sure it works like tha=
t:=20=20
Server2=3D# 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=3D# 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 nee=
d 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=C5=82(=
a):

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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin