Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date formatting

Date formatting

From
Mavka
Date:
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.

Re: Date formatting

From
"Andrei M. Eichler"
Date:
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