Newbie timestamp question

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?

Thanks in advance.

Robin Kopetzky
Black Mesa Computers/Internet Services
Grants, NM

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(at)blackmesa-isp(dot)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'