Subject: Date formatting

From: Mavka <[email-masked]>
Date: Thu, 28 Apr 2016 11:07:53 -0700

To convert a string to date I need to omit meaningless parts. I found
this format working well (Twitter dump format):

SELECT to_timestamp(
  'Tue Sep 24 08:56:18 +0000 2013',
  'xxx Mon DD HH24:MI:SS xxxxx YYYY'
)
-- 2013-09-24 08:56:18-07

In place of "xxx" I can use any non-reserved symbols, e.g.:

'aaa Mon DD HH24:MI:SS bbbbb YYYY'

Number of symbols can vary in some limits, e.g. 'aa' is still working.

Is it hack or normal behaviour of parser? I did not find any mention of
wildcards or placeholders in documentation:

http://www.postgresql.org/docs/9.5/static/functions-formatting.html

Best regards,
Mavka

P.S. I use PostgreSQL 9.4.

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: "Andrei M. Eichler" <[email-masked]>
Date: Fri, 29 Apr 2016 13:04:20 -0300

I don't understand exactly what are your problem.

In the beginning of your email, you said that you want to convert a text to
timestamp, which can be done like this:

select 'Tue Sep 24 08:56:18 +0000 2013'::timestamp
-- 2013-09-24 08:56:18

What you refer as "meaningless parts" are day of week and timezone offset
time, which are Dy and OF options on
http://www.postgresql.org/docs/9.5/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

About ignoring/skipping characters on the format, there is this explanation
in the docs:

Ordinary text is allowed in to_char templates and will be output literally.
You can put a substring in double quotes to force it to be interpreted as
literal text even if it contains pattern key words. For example, in'"Hello
Year "YYYY', the YYYY will be replaced by the year data, but the single Y
in Year will not be. In to_date, to_number, and to_timestamp, double-quoted
strings skip the number of input characters contained in the string, e.g.
"XX" skips two input characters.