CODEWITHSUNDEEP
sqlregexpostgresql
cYn
cYn

Reputation: 3381

Postgres - Removing milliseconds from timestamp text

In my database we have a column that holds data in XML format. And one of the XML elements holds dates that looks like this:

<timestamp>2013-05-22 16:01:26.78 CDT</timestamp>
<timestamp>2013-05-15 10:29:44.588 CDT</timestamp>
<timestamp>2013-02-27 09:37:54.677 CST</timestamp>

So I extracted the value between <timestamp> using:

substring(table.column from '<timestamp>(.*)</timestamp>') as "Time Stamp"

But I'm having a hard time trying to use regular expression to remove just the milliseconds.

What I want is 2013-02-27 09:37:54 CST

I don't know enough regular expressions to filter out the milliseconds.

Upvotes: 0

Views: 915

Answers (2)

bma
bma

Reputation: 9796

Using your existing query, you could cast the result (note the TIMESTAMPTZ(0)):

SELECT substring(col1,'<timestamp>(.*)</timestamp>')::TIMESTAMPTZ(0) as "Time Stamp"
FROM ...

Upvotes: 1

Plasmarob
Plasmarob

Reputation: 1391

shouldn't just using:

/<timestamp>[^\.]*.([^\s]*)\s.*<\/timestamp>/

do it? that's ruby regex, btw.

What you want is "<\timestamp>", "any non-period chars", ".", "any nonwhitespace", "a space", "anything", "</timestamp>"

with "any non-whitespace" selected. (in parens in ruby)

Hope that helps. Dunno how much the regexes vary. some like ruby and perl are identical. Note I have escaped some chars - SO doesn't like the timestamp tag.

Upvotes: 0

Related Questions