Thread: display to_timestamp in quotas or convert to char ?
Hello,
This post might be unreadable in your mail client. If so try to copy it in to notepad as there is a long query
statement.
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
WHEREfiletime < 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
convertedto timestamp.
When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04 22:13:14.000' (NOTE:
singlequotas) it works quite good.
If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character varying" or
displayits value in single quotas ?
Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID FROM ss21_file
WHEREfiletime < '2015-05-04 22:13:14.000';
value | fileindex | filename | filetime | filesizeinrecords
|filesizeinbytes | sourceid
----------------------+-----------+---------------------------------------+-------------------------+-------------------+-----------------+-----------------------------
-------
-903097975225368365 | 94859 | ss21_20141118020501_1166.DAT | 2014-11-18 02:05:30.109 | 2775
| 490560 | coll_SS21-Group_0_141627273
0_1634
-2781431802746610881 | 94868 | ss21_20141118042502_1175.DAT | 2014-11-18 04:25:30.154 | 1803
| 310688 | coll_SS21-Group_0_141628113
0_1643
-4731211533677294393 | 94878 | ss21_20141118065501_1185.DAT | 2014-11-18 06:55:30.380 | 14745 |
2640848| coll_SS21-Group_0_141629013
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
On May 5, 2015, at 5:46 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > This has the advantage of checking "filetime" for correctness. And comparing correctly across different time zones, unlike converting them both to text… -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice
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
>
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of czezz
Sent: Tuesday, May 05, 2015 9:24 AM
To: Albe Laurenz
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN]display to_timestamp in quotas or convert to char ?
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
There is nothing odd about HINT.
to_char(timestamp, text) requires second parameter: format, which you didn't provide.
Regards,
Igor Neyman
On May 5, 2015, at 7:24 AM, czezz <czezz@o2.pl> wrote:
>
> 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.
WHY are you still trying to convert to_char??? Convert the varchar field to_timestamp and be done with it.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
This is because this query is executed/hardcoded in application.
And by executing it like this I can prove devs that there is a problem.
Dnia 5 maja 2015 15:56 Scott Ribe <scott_ribe@elevated-dev.com> napisał(a):
> On May 5, 2015, at 7:24 AM, czezz <czezz@o2.pl> wrote:
> >
> > 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.
>
> WHY are you still trying to convert to_char??? Convert the varchar field to_timestamp and be done with it.
>
>
On May 5, 2015, at 8:21 AM, czezz <czezz@o2.pl> wrote: > > This is because this query is executed/hardcoded in application. > And by executing it like this I can prove devs that there is a problem. So you were really looking for an explanation of why it didn’t work, in order to pass along to people whom you’re havingtrouble convincing? OK, didn’t get that from your post, now it makes sense. It just seemed odd that you seemed to beignoring the most correct (and easy) solution ;-) Anyway, now the we understand that… PostgreSQL used to be more aggressive about automatically casting text types to morespecific types. I suspect, though I don’t recall for sure, that this query would have worked on some earlier versions.Later versions removed some of the automatic casting, because it was found that those casts caused problems—thatsometimes they would cause accidental type mismatches to be executed instead of flagged, and produce incorrectresults instead of an error message, so the decision was made that if you want to compare mismatched types, youhave to be explicit about it. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice
Hi, ya, that is the background of this story. There is one internal app in my company that frequently runs into specific error - related to that query. Its actually not Devs team who reject to look at this but support team (which is one step before devs) So, now showing them this same query with added to_char() they should "shut-the-front-door" and send it to correction. ...though its really difficult with them. Anyway thank you all for your help :) BR, czezz Dnia 5 maja 2015 16:47 Scott Ribe <scott_ribe@elevated-dev.com> napisał(a): > On May 5, 2015, at 8:21 AM, czezz <czezz@o2.pl> wrote: > > > > This is because this query is executed/hardcoded in application. > > And by executing it like this I can prove devs that there is a problem. > > So you were really looking for an explanation of why it didn’t work, in order to pass along to people whom you’re havingtrouble convincing? OK, didn’t get that from your post, now it makes sense. It just seemed odd that you seemed to beignoring the most correct (and easy) solution ;-) > > Anyway, now the we understand that… PostgreSQL used to be more aggressive about automatically casting text types to morespecific types. I suspect, though I don’t recall for sure, that this query would have worked on some earlier versions.Later versions removed some of the automatic casting, because it was found that those casts caused problems—thatsometimes they would cause accidental type mismatches to be executed instead of flagged, and produce incorrectresults instead of an error message, so the decision was made that if you want to compare mismatched types, youhave to be explicit about it. > >
On May 5, 2015, at 9:09 AM, czezz <czezz@o2.pl> wrote: > > ya, that is the background of this story. > … > ...though its really difficult with them. Oh, I hate that—knowing exactly what a bug is, and the exact fix, and not being able to get the message past support! -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice