Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEflb-00066C-V2 for pgsql-sql@postgresql.org; Thu, 20 Sep 2012 12:22:04 +0000 Received: from mx24.belgacom.be ([213.181.45.234]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEflY-0003Ss-Jf for pgsql-sql@postgresql.org; Thu, 20 Sep 2012 12:22:02 +0000 X-IronPort-AV: E=Sophos;i="4.80,453,1344204000"; d="scan'208,217";a="25618688" Received: from unknown (HELO A03005.BGC.NET) ([10.120.129.161]) by mx24.belgacom.be with ESMTP; 20 Sep 2012 14:21:58 +0200 X-TM-IMSS-Message-ID: <03eb20b80000d239@belgacom.be> Received: from A04027.BGC.NET ([10.121.135.24]) by belgacom.be ([10.120.129.161]) with ESMTP (TREND IMSS SMTP Service 7.1; TLSv1/SSLv3 AES128-SHA (128/128)) id 03eb20b80000d239 ; Thu, 20 Sep 2012 14:21:57 +0200 Received: from A04059.BGC.NET ([10.121.135.30]) by A04027.BGC.NET ([10.121.135.24]) with mapi id 14.01.0355.002; Thu, 20 Sep 2012 14:21:57 +0200 From: "BACHELART PIERRE (CIS/SCC)" To: "BACHELART PIERRE (CIS/SCC)" , "pgsql-sql@postgresql.org" Subject: Re: matching a timestamp field Thread-Topic: matching a timestamp field Thread-Index: Ac2XHVl6Z/YUrpUBQZG3Rsr5m58VjgADIEcA Date: Thu, 20 Sep 2012 12:21:56 +0000 Message-ID: <3C56CD7AD881324BB4C60AA5CE2C5CC1172001FC@A04059.BGC.NET> References: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> In-Reply-To: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> Accept-Language: en-US, en-GB Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.111.9.140] Content-Type: multipart/alternative; boundary="_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_" MIME-Version: 1.0 X-Pg-Spam-Score: -2.4 (--) X-Archive-Number: 201209/48 X-Sequence-Number: 36850 --_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, The solution I just found on the Net (Thanks to Samuel Gendler) ansroc=3D# select * from s12hwdb where record::text ~ '2012-09-20 11:50:02'= limit 5; host | exchange | rit | board | var | lceid | pceid | mnem | = eq | rtyp | rv | cetype | record | type | zone ----------+----------+---------+----------+------+-------+-------+-------+-= ---+------+----+----------+---------------------+------+------ and5032t | and5032t | 01a0301 | 21122994 | ebjb | 0000 | 000c | con3a | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0307 | 21406298 | aaca | 0000 | 000c | mmca | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0309 | 21406298 | aaca | 0000 | 000c | mmca | s= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0311 | 21407930 | aaaa | 0000 | 000c | mmcb | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0313 | 21407932 | abca | 0000 | 000c | mcud | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 (5 rows) But I still can not find this in the doc. From: BACHELART PIERRE (CIS/SCC) [mailto:pierre.bachelart@belgacom.be] Sent: Thursday 20 September 2012 13:01 To: pgsql-sql@postgresql.org Subject: matching a timestamp field Hello, Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ??? Is there something I have missed in the doc ? Welcome to psql 8.1.19, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit ansroc=3D# select * from s12hwdb where record ~'2012-09-20' limit 5; host | exchange | rit | board | var | lceid | pceid | mnem | = eq | rtyp | rv | cetype | record | type | zone ----------+----------+---------+----------+------+-------+-------+-------+-= ---+------+----+----------+---------------------+------+------ and5032t | and5032t | 01a0301 | 21122994 | ebjb | 0000 | 000c | con3a | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0307 | 21406298 | aaca | 0000 | 000c | mmca | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0309 | 21406298 | aaca | 0000 | 000c | mmca | s= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0311 | 21407930 | aaaa | 0000 | 000c | mmcb | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 and5032t | and5032t | 01a0313 | 21407932 | abca | 0000 | 000c | mcud | e= | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H | a1 (5 rows) ansroc=3D# \q psql (8.4.9) Type "help" for help. ansroc=3D# select * from s12hwdb where record ~'2012-09-20' limit 5; ERROR: operator does not exist: timestamp without time zone ~ unknown LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5; ^ HINT: No operator matches the given name and argument type(s). You might n= eed to add explicit type casts. ansroc=3D# Pierre. +32 471 68 12 23 ________________________________ ***** Disclaimer ***** http://www.belgacom.be/maildisclaimer --_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello,

 

The solution I just fo= und on the Net (Thanks to Samuel Gendler)

 

ansroc=3D# select * fr= om s12hwdb where record::text ~ '2012-09-20 11:50:02' limit 5;

   host = ;  | exchange |   rit   |  board   = | var  | lceid | pceid | mnem  | eq | rtyp | rv |  cetype&nb= sp; |       record    &nb= sp;   | type | zone

----------+-------= ---+---------+----------+------+-------+-------+---= ----+----+------+----+----------+---------------------&= #43;------+------

and5032t | and5032t | = 01a0301 | 21122994 | ebjb | 0000  | 000c  | con3a | e  | ef0= 3 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1

and5032t | and5032t | = 01a0307 | 21406298 | aaca | 0000  | 000c  | mmca  | e  = | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1

and5032t | and5032t | = 01a0309 | 21406298 | aaca | 0000  | 000c  | mmca  | s  = | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1

and5032t | and5032t | = 01a0311 | 21407930 | aaaa | 0000  | 000c  | mmcb  | e  = | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1

and5032t | and5032t | = 01a0313 | 21407932 | abca | 0000  | 000c  | mcud  | e  = | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1

(5 rows)

 

But I still can not fi= nd this in the doc.

 

 

From: BACHELART PIERRE (CIS/SCC) [mailto:pierre.bachelart@b= elgacom.be]
Sent: Thursday 20 September 2012 13:01
To: pgsql-sql@postgresql.org
Subject: matching a timestamp field

 

Hello,

 

 

Why is my sql below accepted in 8.1.19 and refused i= n 8.4.9 ???

Is there something I have missed in the doc ?

 

 

Welcome to psql 8.1.19, the PostgreSQL interactive t= erminal.

Type:  \copyright for distribution terms

       \h for help wit= h SQL commands

       \? for help wit= h psql commands

       \g or terminate= with semicolon to execute query

       \q to quit=

 

ansroc=3D# select * from s12hwdb where record ~'2012= -09-20' limit 5;

   host   | exchange | &nbs= p; rit   |  board   | var  | lceid | pceid | = mnem  | eq | rtyp | rv |  cetype  |    &= nbsp;  record        | type | zone<= o:p>

----------+----------+---------+--------= --+------+-------+-------+-------+----+------+-= ---+----------+---------------------+------+------

and5032t | and5032t | 01a0301 | 21122994 | ebjb | 00= 00  | 000c  | con3a | e  | ef03 | b1 | plce#xfx | 2012-09-20= 11:50:02 | H    | a1

and5032t | and5032t | 01a0307 | 21406298 | aaca | 00= 00  | 000c  | mmca  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0309 | 21406298 | aaca | 00= 00  | 000c  | mmca  | s  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0311 | 21407930 | aaaa | 00= 00  | 000c  | mmcb  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0313 | 21407932 | abca | 00= 00  | 000c  | mcud  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

(5 rows)

 

ansroc=3D# \q

 

 

 

psql (8.4.9)

Type "help" for help.

ansroc=3D# select * from s12hwdb where record ~'2012= -09-20' limit 5;

ERROR:  operator does not exist: timestamp with= out time zone ~ unknown

LINE 1: select * from s12hwdb where record ~'2012-09= -20' limit 5;

        &nbs= p;            &= nbsp;           &nbs= p;         ^

HINT:  No operator matches the given name and a= rgument type(s). You might need to add explicit type casts.

ansroc=3D#

 

 

 

 

Pierre.

+32 471 68 12 23

 

 


***** Disclaimer *****
http://www.belgacom.be/ma= ildisclaimer

--_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_--