Newbie timestamp question

On Wed, 17 Mar 2004, Robin 'Sparky' Kopetzky wrote:

Good Morning!!

I'm repairing a series of scripts in PHP that use the 'datetime' of MySQL
and converting them to Postgres. Question is this: The datetime format used
in the script is 'YYYYMMDDHHMMSS' as a text string. Do I have to convert
this to the format shown in the Postgres manual: '1999-01-08 04:05:06' for
Postgres to accept the value or can I just pass an integer as 19990108040506
for the timestamp?

You can just seperate the date part from the time part with a space and it
will work:

postgres=# create table test (dt timestamp);
CREATE TABLE
postgres=# insert into test values ('20020202121410');
ERROR: invalid input syntax for type timestamp: "20020202121410"
postgres=# insert into test values ('20020202 121410');
INSERT 20297173 1
postgres=# select * from test;
dt
---------------------
2002-02-02 12:14:10

On Wednesday 17 March 2004 18:52, Robin 'Sparky' Kopetzky wrote:

Good Morning!!

I'm repairing a series of scripts in PHP that use the 'datetime' of MySQL
and converting them to Postgres. Question is this: The datetime format used
in the script is 'YYYYMMDDHHMMSS' as a text string. Do I have to convert
this to the format shown in the Postgres manual: '1999-01-08 04:05:06' for
Postgres to accept the value or can I just pass an integer as
19990108040506 for the timestamp?

You need to pass it a valid timestamp, either in ISO format as you show, or
European/US/... standards (depending on your settings). There's a full list
of formats in the data-types section of the manuals.

--
Richard Huxton
Archonet Ltd

On Wed, Mar 17, 2004 at 11:52:19 -0700,
Robin 'Sparky' Kopetzky <sparkyk@blackmesa-isp.net> wrote:

Good Morning!!

I'm repairing a series of scripts in PHP that use the 'datetime' of MySQL
and converting them to Postgres. Question is this: The datetime format used
in the script is 'YYYYMMDDHHMMSS' as a text string. Do I have to convert
this to the format shown in the Postgres manual: '1999-01-08 04:05:06' for
Postgres to accept the value or can I just pass an integer as 19990108040506
for the timestamp?

You certainly couldn't have it as an integer. Even as type unknown
(which you get by quoting the constant) it doesn't work. You can use
to_timestamp to convert the string. For example:
bruno=> select to_timestamp('19990108040506', 'YYYYMMDDHH24MISS');
to_timestamp
------------------------
1999-01-08 04:05:06+00
(1 row)

Thank you for all your help.

I built two simple functions (extract_timestamp, build_timestamp) to tear
apart a timestamp and put it back together to make the job easier.

Thanks again!

'Sparky'