From: czezz <czezz@o2.pl>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: pgsql-admin@postgresql.org
Subject: RE: display to_timestamp in quotas or convert to char ?
Date: Tue, 05 May 2015 15:24:13 +0200
Message-ID: <70aed348.7ad50e54.5548c47d.1e1de@o2.pl> (raw)
In-Reply-To: <A737B7A37273E048B164557ADEF4A58B365FC46A@ntex2010a.host.magwien.gv.at>
References: <22c93a48.7dc1250c.5548946d.f29b0@o2.pl>
<A737B7A37273E048B164557ADEF4A58B365FC46A@ntex2010a.host.magwien.gv.at>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: czezz@o2.pl, laurenz.albe@wien.gv.at
Subject: RE: display to_timestamp in quotas or convert to char ?
In-Reply-To: <70aed348.7ad50e54.5548c47d.1e1de@o2.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox