Thread: timestamp SQL question

timestamp SQL question

From
Brian Doyle
Date:
I have a table like: 

events visitor_uid varchar(32) event_type  varchar(32) event_type  timestamp

I would like to select events from the table that are older than 30 days
from right now.  How would I do that in one query?  Thanks. 







Re: timestamp SQL question

From
"codeWarrior"
Date:
WHERE event_type < NOW() - interval '30 days';



"Brian Doyle" <bdoyle@localmatters.com> wrote in message 
news:1132269492.30942.20.camel@dev11dt.corp.localmatters.com...
>I have a table like:
>
> events
>  visitor_uid varchar(32)
>  event_type  varchar(32)
>  event_type  timestamp
>
> I would like to select events from the table that are older than 30 days
> from right now.  How would I do that in one query?  Thanks.
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
> 




how to do a find and replace

From
Dawn Buie
Date:
Hello-
I'm using postgres 7.4

I have a column of data with the wrong prefix for many items.

The wrong entries are entered ' /0/v.myimage.jpg'
While the correct ones are ' /0/myimage.jpg'


I need to remove all the 'v.' characters from this column.


I'm able to do a

SELECT * FROM myTable
WHERE location = '%/0/v.%'


I'm just confused about how I should write code to update the selected 
items to remove the 'v.'

Would I use substring? An example would be much appreciated.

thank you
Dawn



Re: how to do a find and replace

From
Bricklen Anderson
Date:
Dawn Buie wrote:
> Hello-
> I'm using postgres 7.4
> 
> I have a column of data with the wrong prefix for many items.
> 
> The wrong entries are entered ' /0/v.myimage.jpg'
> While the correct ones are ' /0/myimage.jpg'
> 
> 
> I need to remove all the 'v.' characters from this column.
> 
> 
> I'm able to do a
> 
> SELECT * FROM myTable
> WHERE location = '%/0/v.%'
> 
> 
> I'm just confused about how I should write code to update the selected
> items to remove the 'v.'
> 
> Would I use substring? An example would be much appreciated.
> 
> thank you
> Dawn

How about update myTable set location=replace(location,'v.','');

-- 
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________


Re: how to do a find and replace

From
Dawn Buie
Date:
I used:

update media_instance set location=replace(location,'v.','')
where location like '%/0/v.%'

and that did work-

thank you very much.

it seems to me that the replace function is the same as translate()- no?

On 17-Nov-05, at 3:55 PM, Bricklen Anderson wrote:

> Dawn Buie wrote:
>> Hello-
>> I'm using postgres 7.4
>>
>> I have a column of data with the wrong prefix for many items.
>>
>> The wrong entries are entered ' /0/v.myimage.jpg'
>> While the correct ones are ' /0/myimage.jpg'
>>
>>
>> I need to remove all the 'v.' characters from this column.
>>
>>
>> I'm able to do a
>>
>> SELECT * FROM myTable
>> WHERE location = '%/0/v.%'
>>
>>
>> I'm just confused about how I should write code to update the selected
>> items to remove the 'v.'
>>
>> Would I use substring? An example would be much appreciated.
>>
>> thank you
>> Dawn
>
> How about update myTable set location=replace(location,'v.','');
>
> -- 
> _______________________________
>
> This e-mail may be privileged and/or confidential, and the sender does
> not waive any related rights and obligations. Any distribution, use or
> copying of this e-mail or the information it contains by other than an
> intended recipient is unauthorized. If you received this e-mail in
> error, please advise me (by return e-mail or otherwise) immediately.
> _______________________________
>



Re: how to do a find and replace

From
Bricklen Anderson
Date:
Dawn Buie wrote:
> I used:
> 
> update media_instance set location=replace(location,'v.','')
> where location like '%/0/v.%'
> 
> and that did work-
> 
> thank you very much.
> 
> it seems to me that the replace function is the same as translate()- no?
> 
Right, I forgot your WHERE clause.

Some more details on those functions can be found here:
http://www.postgresql.org/docs/8.1/interactive/functions-string.html#FUNCTIONS-STRING-OTHER

-- 
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________


Re: how to do a find and replace

From
Hélder M. Vieira
Date:
> update media_instance set location=replace(location,'v.','')
> where location like '%/0/v.%'


For more complex search/replace operations, 8.1 provides an interesting 
function, named 'regexp_replace'.
It probably goes unnoticed because although there is a small reference in 
the manual in section 'E.1.3.6. Data Type and Function Changes'
(http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1) 
and a description in section '9.7.3 POSIX Regular expressions' 
(http://www.postgresql.org/docs/8.1/interactive/functions-matching.html), it 
isn't mentioned in section '9.4. String Functions and Operators' 
(http://www.postgresql.org/docs/8.1/interactive/functions-string.html).


Hélder M. Vieira