Thread: timestamp SQL question
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.
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 >
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
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. _______________________________
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. > _______________________________ >
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. _______________________________
> 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