Migration from DB2 to PostgreSQL-TIMESTAMP(arg1,arg1)

I have done some more try as follows:

#select timestamp(current_date);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date);
======================================================

#SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
======================================================

select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date,current_time);
======================================================

#SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT
TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:4...

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761390.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 27/06/2013 12:51, sachin kotwal wrote:

I have done some more try as follows:

#select timestamp(current_date);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date);
======================================================

If you're trying to convert a string to a timestamp as in your first
email, you probably want to_timestamp():

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

Otherwise, current_timestamp and friends produce the current date & time:

http://www.postgresql.org/docs/9.2/static/functions-datetime.html

HTH,

Ray.

#SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
======================================================

select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date,current_time);
======================================================

#SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT
TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:4...

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761390.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I've done something weird:

CREATE OR REPLACE FUNCTION "timestamp"(_date date, _time time) RETURNS
timestamp AS $$
SELECT _date + _time;
$$ LANGUAGE sql;

SELECT "timestamp"('2013-01-01'::date, '12:00:00'::time);

It worked, but you will need explict cast and quote the timestamp function
name... Many people would not like this.

2013/6/27 Raymond O'Donnell <rod@iol.ie>

On 27/06/2013 12:51, sachin kotwal wrote:

I have done some more try as follows:

#select timestamp(current_date);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date);
======================================================

If you're trying to convert a string to a timestamp as in your first
email, you probably want to_timestamp():

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

Otherwise, current_timestamp and friends produce the current date & time:

http://www.postgresql.org/docs/9.2/static/functions-datetime.html

HTH,

Ray.

#SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'));
======================================================

select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date,current_time);
======================================================

#SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT
TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','YYYY-MM-DD'),'17:15:4...

--
View this message in context:

http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761390.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル

On 2013-06-27 20:43, sachin kotwal wrote:

While migrating my application from DB2 to PostgreSQL.

I want to migrate TIMESTAMP() function of DB2 into PostgreSQL.

Example in DB2:
#SELECT TIMESTAMP('2013-01-01','12:13:14') FROM SYSIBM.SYSDUMMY1

1
--------------------------
2013-01-01-12.13.14.000000

1 record(s) selected.
==========================

Example PostgreSQL:

#SELECT TIMESTAMP('2013-01-01','12:13:14');
ERROR: syntax error at or near "'2013-01-01'" at character 18
STATEMENT: SELECT TIMESTAMP('2013-01-01','12:13:14');
ERROR: syntax error at or near "'2013-01-01'"
LINE 1: SELECT TIMESTAMP('2013-01-01','12:13:14');

After executing above SQL I am getting error.

I checked definition of TIMESTAMP().
It is as follows:
test=# \df timestamp
List of functions
Schema | Name | Result data type | Argument
data types | Type
------------+-----------+-----------------------------+--------------------------------------+--------
pg_catalog | timestamp | timestamp without time zone | abstime
| normal
pg_catalog | timestamp | timestamp without time zone | date
| normal
pg_catalog | timestamp | timestamp without time zone | date, time without
time zone | normal
pg_catalog | timestamp | timestamp without time zone | timestamp without
time zone, integer | normal
pg_catalog | timestamp | timestamp without time zone | timestamp with time
zone | normal
(5 rows)

As we can see TIMESTAMP() hast two arguments date and time without time
stamp but still it is giving error to me.

Please reply if any suggestion.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,

# SELECT pg_catalog.timestamp('2013-01-01'::date, '12:13:14'::time);
timestamp
---------------------
2013-01-01 12:13:14
(1 row)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 6/27/2013 4:51 AM, sachin kotwal wrote:

#select timestamp(current_date);

try... current_date::timestamptz
or.... cast current_date as timestamptz

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Using your link
http://www.postgresql.org/docs/9.2/static/functions-formatting.html

In DB2 when I use following command I am getting output combined date and
time i passed to function.

#SELECT TIMESTAMP('2013-01-01','12:13:14') FROM SYSIBM.SYSDUMMY1

1
--------------------------
2013-01-01-12.13.14.000000

1 record(s) selected.
==========================

If I execute same command with TO_TIMESTAMP() function I am getting default
date.

SELECT TO_TIMESTAMP('2013-01-01','12:13:14');
to_timestamp
---------------------------------
0001-01-01 00:00:00+05:53:28 BC
(1 row)

So output is different, so I can not use TO_TIMESTAMP() function as it is.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761626.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I've done something weird:
CREATE OR REPLACE FUNCTION "timestamp"(_date date, _time time) RETURNS

timestamp AS $$

SELECT _date + _time;
$$ LANGUAGE sql;

SELECT "timestamp"('2013-01-01'::date, '12:00:00'::time);

Good one.

function with above definition is already present in pg_catalog. so no need
to define new function.(3 rd row)

\df timestamp
List of functions
Schema | Name | Result data type | Argument
data types | Type
------------+-----------+-----------------------------+--------------------------------------+--------
pg_catalog | timestamp | timestamp without time zone | abstime
| normal
pg_catalog | timestamp | timestamp without time zone | date
| normal
pg_catalog | timestamp | timestamp without time zone | date, time without
time zone | normal
pg_catalog | timestamp | timestamp without time zone | timestamp without
time zone, integer | normal
pg_catalog | timestamp | timestamp without time zone | timestamp with time
zone | normal
(5 rows)

But why this function requires "" to get execute.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761628.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general