Thread: substring ..

substring ..

From
Jeff MacDonald
Date:
hi folks..

i want to do this to a datetime field..

select foo from table where substr(datefoo,1,11) = '2000-12-14';

it returns no results yet..

select substr(datefoo,1,11) does return some values that say
2000-12-14

any clues ?

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc        | Hub.Org Networking Services
jeff@pgsql.com        | jeff@hub.org
www.pgsql.com        | www.hub.org
1-902-542-0713        | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick  : bignose
PGP Public Key : http://bignose.hub.org/public.txt



RE: substring ..

From
"Francis Solomon"
Date:
Hi Jeff,

'2000-12-14' is only 10 chars long.
You're asking for an 11-char long substring to match a 10-char ... not
going to happen!

You can see this better if you do something like this ...
select '@' || substr(datefoo,1,11) || '@' from table;

... and you'll get results like:

@2000-12-14 @

So, you could modify your query to do:

select foo from table where substr(datefoo, 1, 10) = '2000-12-14';

Alternatively, what's wrong with this approach?

select foo from table where date(datefoo) = '2000-12-14';

I think that might execute a little faster.

Hope this helps

Francis Solomon

> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
>
> Jeff MacDonald,



Re: substring ..

From
Stephan Szabo
Date:
On Tue, 19 Dec 2000, Jeff MacDonald wrote:

> hi folks..
> 
> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
> 
> it returns no results yet..
> 
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
> 
> any clues ?

My guess is that it's a trailing space thing.  '2000-12-14' is
only 10 characters not 11...  What you're actually getting is
probably '2000-12-14 '




Re: substring ..

From
Josh Berkus
Date:
Jeff,

> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
> 
> it returns no results yet..
> 
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14

Well, for one it's not a string, it's a datetime field.

WHy are you trying to substring a datetime field, anyway?
                -Josh 

-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small
businesses      fax  436-0137   and non-profit organizations.       pager 338-4078                               San
Francisco


Re: substring ..

From
"Joel Burton"
Date:
> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
> 
> it returns no results yet..
> 
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14

Ummm... because '2000-12-14' is a ten-character, not eleven 
character long string. Try substr(datefoo,1,10) and it works for me 
(under 7.1devel).

However, this all seems sloppy. Why not extract the date, and 
compare it as a date?

--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)


Re: substring ..

From
Tulio Oliveira
Date:
Jeff MacDonald wrote:
> 
> hi folks..
> 
> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
> 
> it returns no results yet..
> 
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
> 
> any clues ?
> 
> Jeff MacDonald,
> 
> -----------------------------------------------------
> PostgreSQL Inc          | Hub.Org Networking Services
> jeff@pgsql.com          | jeff@hub.org
> www.pgsql.com           | www.hub.org
> 1-902-542-0713          | 1-902-542-3657
> -----------------------------------------------------
> Facsimile : 1 902 542 5386
> IRC Nick  : bignose
> PGP Public Key : http://bignose.hub.org/public.txt



try remove the "-" from your WHERE clausule...

This signal appears only in SELECT results...
-- 
======================================================
AKACIA TECNOLOGIA
Desenvolvimento de sistemas para Internet
www.akacia.com.br


Re: substring ..

From
"Robert B. Easter"
Date:
Hehe, here is my tests with this:

ctntest2=# SELECT create_date FROM users;     create_date
------------------------2000-08-29 13:01:53-042000-08-27 20:04:41-042000-08-27 21:24:28-042000-08-30
09:51:16-042000-07-2523:14:08-042000-07-25 23:14:08-042000-09-01 02:53:02-042000-07-25 23:14:08-04
 
(8 rows)
ctntest2=# SELECT substr(create_date,1,10) FROM users;  substr
------------2000-08-292000-08-272000-08-272000-08-302000-07-252000-07-252000-09-012000-07-25
(8 rows)
ctntest2=# SELECT create_date FROM users WHERE substr(create_date,1,7) = 
'2000-08';     create_date
------------------------2000-08-29 13:01:53-042000-08-27 20:04:41-042000-08-27 21:24:28-042000-08-30 09:51:16-04
(4 rows)                                                      


Seems to work fine.  My fields are TIMESTAMP.  If you really still have 
trouble, then try a cast: substr(datefoo::TEXT,1,10)   ??



On Tuesday 19 December 2000 14:06, Jeff MacDonald wrote:
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
>
> Jeff MacDonald,
>
> -----------------------------------------------------
> PostgreSQL Inc        | Hub.Org Networking Services
> jeff@pgsql.com        | jeff@hub.org
> www.pgsql.com        | www.hub.org
> 1-902-542-0713        | 1-902-542-3657
> -----------------------------------------------------
> Facsimile : 1 902 542 5386
> IRC Nick  : bignose
> PGP Public Key : http://bignose.hub.org/public.txt

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------


Re: substring ..

From
Karel Zak
Date:
On Tue, 19 Dec 2000, Jeff MacDonald wrote:

> hi folks..
> 
> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
And why not to_char()?
            Karel



Re: substring ..

From
Jie Liang
Date:
Hi,there,

I am not sure what is your question mean. However,
if the type of datefoo is a timestamp then try:
select foo from table where date(datefoo) = '2000-12-14';
select foo from table where datefoo::date = '2000-12-14'::date;

select foo from table where substr(datefoo,1,10) = '2000-12-14';
might work also.

Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Tue, 19 Dec 2000, Jeff MacDonald wrote:

> hi folks..
> 
> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
> 
> it returns no results yet..
> 
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
> 
> any clues ?
> 
> Jeff MacDonald,
> 
> -----------------------------------------------------
> PostgreSQL Inc        | Hub.Org Networking Services
> jeff@pgsql.com        | jeff@hub.org
> www.pgsql.com        | www.hub.org
> 1-902-542-0713        | 1-902-542-3657
> -----------------------------------------------------
> Facsimile : 1 902 542 5386
> IRC Nick  : bignose
> PGP Public Key : http://bignose.hub.org/public.txt
>