Thread: Migration from DB2 to PostgreSQL-TIMESTAMP(arg1,arg1)
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.
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.
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
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:If you're trying to convert a string to a timestamp as in your first
> 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);
> ======================================================
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.Raymond O'Donnell :: Galway :: Ireland
>
> #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.
>
>
--
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)
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
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.
>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.