Regex remove "-" from middle of timestamp line in postgresql
I have a table with a column hour
that is not in the correct format timestamp
:
hour
---------------------
2014-12-06-01:44:35
2014-12-06-01:44:35
2014-12-06-01:44:35
2014-12-06-01:44:35
2014-12-06-01:44:35
2014-12-06-01:44:35
I want to replace -
in the ' '
middle, so it looks like this:
hour
---------------------
2014-12-06 01:44:35
2014-12-06 01:44:35
2014-12-06 01:44:35
2014-12-06 01:44:35
2014-12-06 01:44:35
2014-12-06 01:44:35
I've tried this:
select regexp_replace(hour, '\d{4}-\d{2}-\d{2}(-)\d{2}:\d{2}:\d{2}', ' ')
from my_table;
Unfortunately, it doesn't matter.
I am using Redshift, not PostgreSQL.
source to share
There are several things to think about when creating Regex expressions in postgresql. First, you need to avoid backslashes. So instead \
you need \\
. Second, you can refer to capturing groups by using \ n to capture the nth group in parentheses. You can use a capturing group in your override value to refer to parts of the original string.
Putting it all together, this is what you need:
regexp_replace('hour', '(\\d{4}-\\d{2}-\\d{2})-(\\d{2}:\\d{2}:\\d{2})', '\\1 \\2')
source to share
Limited options in Redshift
regexp_replace(hour, '(^\\d{4}-\\d{2}-\\d{2})-(\\d{2}:\\d{2}:\\d{2}$)', '\\1 \\2') AS a
regexp_replace(hour, '(^\\d{4}-\\d\\d-\\d\\d)-(\\d\\d:\\d\\d:\\d\\d)$', '\\1 \\2') AS b
regexp_replace(hour, '(^[\\d-]{10})-([\\d:]+)$', '\\1 \\2') AS c
left(hour,10) || ' ' || substring(hour FROM 12) AS e
Advanced options in modern Postgres (9.1+)
regexp_replace(hour, '(^\d{4}-\d{2}-\d{2})-(\d{2}:\d{2}:\d{2}$)', '\1 \2') AS a
regexp_replace(hour, '(^\d{4}-\d\d-\d\d)-(\d\d:\d\d:\d\d)$', '\1 \2') AS b
regexp_replace(hour, '(^[\d-]{10})-([\d:]+)$', '\1 \2') AS c
reverse(regexp_replace(reverse(hour), '-', ' ')) AS d
left(hour,10) || ' ' || right(hour, -11) AS e
overlay(hour placing ' ' from 11) AS f
to_timestamp(hour, 'YYYY-MM-DD-HH24:MI:SS') AS ts
From "restrictive" to "cheap" in order of appearance. ts
is special.
and
This is like the currently accepted answer from @Zeki , completed with anchors at the beginning and end with ^
and $
to make it even less ambiguous and potentially faster.
You want special meaning \d
as a class abbreviation for numbers.
In Postgres, don't escape backslashes \
with \\
. This would be incorrect if you are not working with an outdated legacy setting standard_conforming_strings = off
. Redshift is stuck in an old development stage and does just that. Backslashes are interpreted unless escaped with another backslash.
B
\d\d
shorter and cheaper than \d{2}
.
C
Simplify character classes: numbers + hyphen: [\d-]
and numbers + a colon: [\d:]
.
d
Since regexp_replace()
without the 4th parameter 'g'
only replaces the first match, you can reverse()
string, replace the first hyphen and reverse()
back.
Doesn't work in Redshift because it uses a simpler versionregexp_replace()
that always replaces all occurrences.
e
If the format is fixed as shown, just take the first 10 characters, a space and the rest of the line.
Redshift uses simpler versions left()
andright()
that don't take negative parameters, so I replaced substring()
.
e
Or, even easier, just overlay () the 11th character with a space.
Not implemented in Redshift.
c
Unlike the rest, it returns the correct type, not . You can also assign the result . More details. ... By far the best option if you want to convert your string.
Not implemented in Redshift. to_timestamp()
timestamp with time zone
text
timestamp without time zone
source to share