MIME-Version: 1.0
In-Reply-To: <20160428110753.2700f00d@nikita_desk>
References: <20160428110753.2700f00d@nikita_desk>
Date: Fri, 29 Apr 2016 13:04:20 -0300
Message-ID: 
 <CABS=F1KXirKd4CAbcAYv5j+NoXyCEp=sRHuMhqart303BPVgyw@mail.gmail.com>
Subject: Re: Date formatting
From: "Andrei M. Eichler" <andrei.eichler@unicheck.com.br>
To: Mavka <mavka_temp@mail.ru>
Cc: pgsql-docs@postgresql.org
Content-Type: multipart/alternative; boundary=001a113ed110b428280531a1cbaf
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

--001a113ed110b428280531a1cbaf
Content-Type: text/plain; charset=UTF-8

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.

2016-04-28 15:07 GMT-03:00 Mavka <mavka_temp@mail.ru>:

> 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 (pgsql-docs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs
>

--001a113ed110b428280531a1cbaf
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>I don&#39;t understand exactly what are your problem.=
</div><div><br></div><div>In the beginning of your email, you said that you=
 want to convert a text to timestamp, which can be done like this:</div><di=
v><br></div><div>select &#39;Tue Sep 24 08:56:18 +0000 2013&#39;::timestamp=
<br></div><div>-- 2013-09-24 08:56:18</div><div><br></div><div>What you ref=
er as &quot;meaningless parts&quot; are day of week and timezone offset tim=
e, which are Dy and OF options on <a href=3D"http://www.postgresql.org/docs=
/9.5/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE">=
http://www.postgresql.org/docs/9.5/static/functions-formatting.html#FUNCTIO=
NS-FORMATTING-DATETIME-TABLE</a></div><div><br></div><div>About ignoring/sk=
ipping characters on the format, there is this explanation in the docs:</di=
v><div><br></div>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 b=
e interpreted as literal text even if it contains pattern key words. For ex=
ample, in&#39;&quot;Hello Year &quot;YYYY&#39;, the YYYY will be replaced b=
y the year data, but the single Y in Year will not be. In to_date, to_numbe=
r, and to_timestamp, double-quoted strings skip the number of input charact=
ers contained in the string, e.g. &quot;XX&quot; skips two input characters=
.</div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">2016-04-28=
 15:07 GMT-03:00 Mavka <span dir=3D"ltr">&lt;<a href=3D"mailto:mavka_temp@m=
ail.ru" target=3D"_blank">mavka_temp@mail.ru</a>&gt;</span>:<br><blockquote=
 class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc soli=
d;padding-left:1ex">To convert a string to date I need to omit meaningless =
parts. I found<br>
this format working well (Twitter dump format):<br>
<br>
SELECT to_timestamp(<br>
=C2=A0 &#39;Tue Sep 24 08:56:18 +0000 2013&#39;,<br>
=C2=A0 &#39;xxx Mon DD HH24:MI:SS xxxxx YYYY&#39;<br>
)<br>
-- 2013-09-24 08:56:18-07<br>
<br>
In place of &quot;xxx&quot; I can use any non-reserved symbols, e.g.:<br>
<br>
&#39;aaa Mon DD HH24:MI:SS bbbbb YYYY&#39;<br>
<br>
Number of symbols can vary in some limits, e.g. &#39;aa&#39; is still worki=
ng.<br>
<br>
Is it hack or normal behaviour of parser? I did not find any mention of<br>
wildcards or placeholders in documentation:<br>
<br>
<a href=3D"http://www.postgresql.org/docs/9.5/static/functions-formatting.h=
tml" rel=3D"noreferrer" target=3D"_blank">http://www.postgresql.org/docs/9.=
5/static/functions-formatting.html</a><br>
<br>
Best regards,<br>
Mavka<br>
<br>
P.S. I use PostgreSQL 9.4.<br>
<span class=3D"HOEnZb"><font color=3D"#888888"><br>
<br>
--<br>
Sent via pgsql-docs mailing list (<a href=3D"mailto:pgsql-docs@postgresql.o=
rg">pgsql-docs@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-docs" rel=3D"noreferrer=
" target=3D"_blank">http://www.postgresql.org/mailpref/pgsql-docs</a><br>
</font></span></blockquote></div><br></div>

--001a113ed110b428280531a1cbaf--