Postgres Datetime Replace regex

The documentation is very helpful for this topic.

Typing add 1 hour timestamp into the search box at:

http://www.postgresql.org/docs/9.4/interactive/index.html

yields the following page as the first hit:

http://www.postgresql.org/docs/9.4/static/functions-datetime.html

So you can do something like

timestamp '2001-09-28 01:00' + interval '1 hour'

Also if your source is a column (namely mydatecolumn) , this could become:

mydatecolumn::timestamp + interval '1 hour'

I'm not exactly sure what you're trying to do, but if the only thing, you're trying to do is to add one hour to starttime timestamp, do not use regexp for date/time manipulations. Just don't.

Adding one hour

Regexps are for manipulating text data, but timestamp is a TIMESTAMP - it has it's internal structure which is more complex and allows precise manipulation, while it's text representation is only a "printed out" version of the original data. Again - TIMESTAMP is postgresql datatype, so manipulate it directly, not via its "printed out" text form using regexp.

To be more specific, you should do something like this:

UPDATE
   events 
SET
   starttime = starttime + interval '1 hour'
WHERE
   account_id = 9 

But if I didn't get it and you want to change only 08:00 times (as would your ill-formed update statement suggest), you can use EXTRACT(field FROM source):

UPDATE
   events 
SET
   starttime = starttime + interval '1 hour'
WHERE
   account_id = 9 AND EXTRACT(hour FROM starttime) = 8

(And similary for minutes or seconds, etc.)

Invalid regexp

Even if using regexps to manipulate TIMEDATE is not very wise, your regexp expression is wrong in several ways:

1. you have 2 regexps in regexp_replace call, which has form of regexp_replace(string, regexp, replacement). But you used regexp string even for replacement, which does this:

   select regexp_replace('2015-11-20 08:00:00',
       E'[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-0]{1}[8-8]{1}:[0-0]{1,2}:[0-0]{1,2}',
       E'[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-0]{1}[9-9]{1}:[0-0]{1,2}:[0-0]{1,2}', 'g');
                               regexp_replace                             
   -----------------------------------------------------------------------
    [0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-0]{1}[9-9]{1}:[0-0]{1,2}:[0-0]{1,2}
   

   So you can see that you just replaced everything with the replacement string. Bettew replacement string would have references to groups matched by a regexp, which brings us to second problem:

2. your regexp expression does not have groups in parenthesis. If I correct that and add references to these groups in replacement string, you'll get:

   select regexp_replace('2015-11-20 08:00:00',
       E'([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) ([0-0]{1})([8-8]{1}):([0-0]{1,2}):([0-0]{1,2})',
       E'\\1-\\2-\\3 \\4\\5:\\6:\\7', 'g');
      regexp_replace    
   ---------------------
    2015-11-20 08:00:00
   

   which looks almost good. But since you want to replace the 08:00, let's change it to:

   select regexp_replace('2015-11-20 08:00:00',
       E'([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) ([0-0]{1})([8-8]{1}):([0-0]{1,2}):([0-0]{1,2})',
       E'\\1-\\2-\\3 09:00:00', 'g');
      regexp_replace    
   ---------------------
    2015-11-20 09:00:00
   

3. this is not exactly a problem, but using [0-0]{1} to match a zero (or other single character) is unnecessary, plain 0 would suffice, so I just added the 08:00:00 string exactly. (You can change it to [01][0-9]:00:00 to match every hour, or [0-9]{2}:[0-9]{2}:[0-9]{2} to match any time)

   select regexp_replace('2015-11-20 08:00:00',
       E'([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) 08:00:00',
       E'\\1-\\2-\\3 09:00:00', 'g');
      regexp_replace    
   ---------------------
    2015-11-20 09:00:00