CODEWITHSUNDEEP
regexpostgresqldatetime
Remington Charles
Remington Charles

Reputation: 123

Postgres Datetime Replace regex

I've gotten as far as possible alone.

I need to replace the time within a datetime stamp in postgres.

This:

2015-11-20 08:00:00

Needs to be this:

2015-11-20 09:00:00

but for every day of the year (only the time changes)

this is what I have so far. (am i even close?)

UPDATE
   events 
SET
   starttime = regexp_replace(starttime,
   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’)            
WHERE
   account_id = 9           
   AND starttime ~ 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}’;

Upvotes: 2

Views: 2413

Answers (3)

Lucas
Lucas

Reputation: 1516

I am hoping that your starttime column is of timestamp or timestamp with time zone but in the event that I suspect it isn't it may need casting.

UPDATE events SET
    starttime = starttime::timestamp + '1 hour'::interval
WHERE account_id = 9 AND extract(hour from starttime::timestamp) = 8;

As with Remi's answer, you should refer to the manual for more information on date time functions.

Upvotes: 4

Jan Spurny
Jan Spurny

Reputation: 5537

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

Upvotes: 4

Rémi Becheras
Rémi Becheras

Reputation: 15222

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'

Upvotes: 4

Related Questions