Обсуждение: How to insert "date" as timestamp

hii,


I am newbie for jdbc-postgres and looking for some help about timestamp
type of posgresql.....


my porgram makes a data object, and then insert this object to the db.
however, I couldnt been able to insert date object directly to the db as
timestamp ...

do I making something wrong ? .. or is there another type to keep full
date of java, in the Postgresql Db.

Thank You.

Hi,

Is your object a java.util.Date, a java.sql.Date, or a
java.sql.Timestamp?  Do you want the column in the database to
store only a date, or combined date and time?  If you want the
column to store a combined date and time, you should define it
as TIMESTAMP WITH TIME ZONE unless you have some very
unusual need to have a timestamp that represents a different
moment in each time zone.

Some sample code and your exception (with stack trace) would
help, too.

-Kevin

>>> Ayd*n Toprak <aydin.toprak@intengo.com> 10/13/05 1:20 AM >>>
hii,


I am newbie for jdbc-postgres and looking for some help about timestamp
type of posgresql.....


my porgram makes a data object, and then insert this object to the db.
however, I couldnt been able to insert date object directly to the db as
timestamp ...

do I making something wrong ? .. or is there another type to keep full
date of java, in the Postgresql Db.

Thank You.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kevin Grittner schrieb:
> Hi,
>
> Is your object a java.util.Date, a java.sql.Date, or a
> java.sql.Timestamp?  Do you want the column in the database to
> store only a date, or combined date and time?  If you want the
> column to store a combined date and time, you should define it
> as TIMESTAMP WITH TIME ZONE unless you have some very
> unusual need to have a timestamp that represents a different
> moment in each time zone.
>

You should have mentioned, that for the correct use of timestamp with
timezone, he needs the jdbc-driver from the CVS.

As far as I know, the bugfixes are not in the released drivers, yet.

And I would use TIMESTAMP without a time zone, in the case I know all
database clients are in the same time zone.

Regards,
Roland.
--
Roland Walter                  phone: +49 (0) 22 25 / 88 2-41 1
MOSAIC SOFTWARE AG               fax: +49 (0) 22 25 / 88 2-20 1
Am Pannacker 3                mailto: rwa (at) mosaic-ag (dot) com
D-53340 Meckenheim              http://www.mosaic-ag.com
-------  L E G A L    D I S C L A I M E R  ---------

Die Informationen in dieser Nachricht sind vertraulich
und ausschliesslich fuer den Adressaten bestimmt.
Kenntnisnahme durch Dritte ist unzulaessig. Die
Erstellung von Kopien oder das Weiterleiten an weitere,
nicht originaere und benannte Adressaten ist nicht
vorgesehen und kann ungesetzlich sein. Die Meinungen
in dieser Nachricht stellen lediglich die Meinungen
des Senders dar. Falls Sie vermuten, dass diese
Nachricht veraendert wurde, setzen Sie sich mit dem
Absender in Verbindung. Der Absender uebernimmt ohne
weitere Ueberpruefung keine Verantwortung fuer die
Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte
Empfaenger werden gebeten, die Vertraulichkeit der
Nachricht zu wahren und den Absender sofort ueber
einen Uebertragungsfehler zu informieren.
------------------------------------------------------

Hi,

My Object is java.util.Date ... first of all;
I make an instance of the Date object

Date date = new Date();

then

I am wirting the set methods of the preparedstatement as

query.setDate(1,date);

however eclipse gives an error message, which is "The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, Date)"...
<b>(but eclipse say that it can!)</b>

so sorry for stack trace but I cant even compile it yet...

I need time of the day, and the date... timezone and other staff  is not important...

13/10/2005 11:30 .. or something like this is enough for me ...



Kevin Grittner wrote:

Hi,

Is your object a java.util.Date, a java.sql.Date, or a
java.sql.Timestamp?  Do you want the column in the database to
store only a date, or combined date and time?  If you want the
column to store a combined date and time, you should define it
as TIMESTAMP WITH TIME ZONE unless you have some very
unusual need to have a timestamp that represents a different
moment in each time zone.

Some sample code and your exception (with stack trace) would
help, too.

-Kevin
 

Ayd*n Toprak <aydin.toprak@intengo.com> 10/13/05 1:20 AM >>>       

hii,


I am newbie for jdbc-postgres and looking for some help about timestamp 
type of posgresql.....


my porgram makes a data object, and then insert this object to the db.
however, I couldnt been able to insert date object directly to the db as 
timestamp ...

do I making something wrong ? .. or is there another type to keep full 
date of java, in the Postgresql Db.

Thank You.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


 

Aydın Toprak schrieb:

> Hi,
>
> My Object is java.util.Date ... first of all;
> I make an instance of the Date object
>
> Date date = new Date();
>
> then
>
> I am wirting the set methods of the preparedstatement as
>
> query.setDate(1,date);
>
> however eclipse gives an error message, which is "The method
> setDate(int, Date) in the type PreparedStatement is not applicable for
> the arguments (int, Date)"...
> <b>(but eclipse say that it can!)</b>
>

You need to convert java.util.Date to java.sql.Date if you want only to
store the date without the time in a database field DATE. Then you can
use setDate().

If you want to store it in a database field TIMESTAMP you must convert
the java.util.Date to java.sql.Timestamp. Then you can use

query.setTimestamp(1, timestamp);

If you use setDate with an java.sql.Date here, you loose the information
of the time, even if the Database field is of the type TIMESTAMP.

The conversion works as the following, i. e.:

java.util.Date date = new java.util.Date(System.currentTimeMillis());
java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());

It is the same for the conversion to java.sql.Date.

Regards,
Roland.

--
Roland Walter                  phone: +49 (0) 22 25 / 88 2-41 1
MOSAIC SOFTWARE AG               fax: +49 (0) 22 25 / 88 2-20 1
Am Pannacker 3                mailto: rwa (at) mosaic-ag (dot) com
D-53340 Meckenheim              http://www.mosaic-ag.com

-------  L E G A L    D I S C L A I M E R  ---------

Die Informationen in dieser Nachricht sind vertraulich
und ausschliesslich fuer den Adressaten bestimmt.
Kenntnisnahme durch Dritte ist unzulaessig. Die
Erstellung von Kopien oder das Weiterleiten an weitere,
nicht originaere und benannte Adressaten ist nicht
vorgesehen und kann ungesetzlich sein. Die Meinungen
in dieser Nachricht stellen lediglich die Meinungen
des Senders dar. Falls Sie vermuten, dass diese
Nachricht veraendert wurde, setzen Sie sich mit dem
Absender in Verbindung. Der Absender uebernimmt ohne
weitere Ueberpruefung keine Verantwortung fuer die
Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte
Empfaenger werden gebeten, die Vertraulichkeit der
Nachricht zu wahren und den Absender sofort ueber
einen Uebertragungsfehler zu informieren.
------------------------------------------------------

Thanks for your help.
It was very accurate and worked.


Roland Walter wrote:

> Aydın Toprak schrieb:
>
>> Hi,
>>
>> My Object is java.util.Date ... first of all;
>> I make an instance of the Date object
>>
>> Date date = new Date();
>>
>> then
>>
>> I am wirting the set methods of the preparedstatement as
>>
>> query.setDate(1,date);
>>
>> however eclipse gives an error message, which is "The method
>> setDate(int, Date) in the type PreparedStatement is not applicable
>> for the arguments (int, Date)"...
>> <b>(but eclipse say that it can!)</b>
>>
>
> You need to convert java.util.Date to java.sql.Date if you want only
> to store the date without the time in a database field DATE. Then you can
> use setDate().
>
> If you want to store it in a database field TIMESTAMP you must convert
> the java.util.Date to java.sql.Timestamp. Then you can use
>
> query.setTimestamp(1, timestamp);
>
> If you use setDate with an java.sql.Date here, you loose the information
> of the time, even if the Database field is of the type TIMESTAMP.
>
> The conversion works as the following, i. e.:
>
> java.util.Date date = new java.util.Date(System.currentTimeMillis());
> java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
>
> It is the same for the conversion to java.sql.Date.
>
> Regards,
> Roland.
>

Good point regarding the recent timestamp fixes, although
I think they are in the dev402 jar, so one need not go to CVS
and do a build.

I definitely would not use timestamp without time zone even
within a single time zone unless that time zone didn't use
daylight saving time, or I was very confident that I didn't need
to record times in the wee hours on days when the time
shifted.  It's always safer to use timestamp with time zone
when what you want to record is an instant in time.

The only use case I've been able to think of where timestamp
without time zone is semantically correct is where a publisher
sets a release for, say, the next Harry Potter book.  Having
attended a late night book release party this summer so that
my kids could pick up their book as soon as 12:01 a.m. rolled
into our time zone, I can see at least one use case for this
data type.

-Kevin


>>> Roland Walter <rwa@mosaic-ag.com> 10/13/05 3:08 AM >>>
And I would use TIMESTAMP without a time zone, in the case I know all
database clients are in the same time zone.

Be careful about that with java.sql.Date.
To demonstrate the problem:

public class TestDate
{
    public static void main(String[] args)
        throws InterruptedException
    {
        java.util.Date utilDate = new java.util.Date(System.currentTimeMillis());
        java.sql.Date sqlDate1 = new java.sql.Date(utilDate.getTime());
        Thread.sleep(20L);
        utilDate = new java.util.Date(System.currentTimeMillis());
        java.sql.Date sqlDate2 = new java.sql.Date(utilDate.getTime());
        System.out.println(sqlDate1 + " equals " + sqlDate2 + " ?  " + sqlDate1.equals(sqlDate2));

        java.sql.Timestamp ts = new java.sql.Timestamp(sqlDate1.getTime());
        System.out.println(ts);
        ts = new java.sql.Timestamp(sqlDate2.getTime());
        System.out.println(ts);
    }
}

I get the following results:

2005-10-13 equals 2005-10-13 ?  false
2005-10-13 09:17:40.431
2005-10-13 09:17:40.461

Unfortunately, the burden is on the application programmer
to provide a ms value which is at midnight for the default
time zone for the JVM when using that constructor. I can't
understand that as a design choice, but that's the current
reality.

-Kevin


>>> Roland Walter <rwa@mosaic-ag.com> 10/13/05 4:10 AM >>>

The conversion works as the following, i. e.:

java.util.Date date = new java.util.Date(System.currentTimeMillis());
java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());

It is the same for the conversion to java.sql.Date.

Kevin Grittner schrieb:

...
>
> I definitely would not use timestamp without time zone even
> within a single time zone unless that time zone didn't use
> daylight saving time, or I was very confident that I didn't need
> to record times in the wee hours on days when the time
> shifted.  It's always safer to use timestamp with time zone
> when what you want to record is an instant in time.
>
> The only use case I've been able to think of where timestamp
> without time zone is semantically correct is where a publisher
> sets a release for, say, the next Harry Potter book.  Having
> attended a late night book release party this summer so that
> my kids could pick up their book as soon as 12:01 a.m. rolled
> into our time zone, I can see at least one use case for this
> data type.
>

I have one. I get timestamps without timezone in a CSV-file
with exported data of transactions from a so called 'Clearing-Center'
that converts EDIFACT-Messages. This data gets imported into the
database of the system that creates the bills. I can not provide
information that I do not have. So there is no use for timestamps with
timezones.

>
>
>
>>>>Roland Walter <rwa@mosaic-ag.com> 10/13/05 3:08 AM >>>
>
> And I would use TIMESTAMP without a time zone, in the case I know all
> database clients are in the same time zone.
>


--
Roland Walter                  phone: +49 (0) 22 25 / 88 2-41 1
MOSAIC SOFTWARE AG               fax: +49 (0) 22 25 / 88 2-20 1
Am Pannacker 3                mailto: rwa (at) mosaic-ag (dot) com
D-53340 Meckenheim              http://www.mosaic-ag.com
-------  L E G A L    D I S C L A I M E R  ---------

Die Informationen in dieser Nachricht sind vertraulich
und ausschliesslich fuer den Adressaten bestimmt.
Kenntnisnahme durch Dritte ist unzulaessig. Die
Erstellung von Kopien oder das Weiterleiten an weitere,
nicht originaere und benannte Adressaten ist nicht
vorgesehen und kann ungesetzlich sein. Die Meinungen
in dieser Nachricht stellen lediglich die Meinungen
des Senders dar. Falls Sie vermuten, dass diese
Nachricht veraendert wurde, setzen Sie sich mit dem
Absender in Verbindung. Der Absender uebernimmt ohne
weitere Ueberpruefung keine Verantwortung fuer die
Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte
Empfaenger werden gebeten, die Vertraulichkeit der
Nachricht zu wahren und den Absender sofort ueber
einen Uebertragungsfehler zu informieren.
------------------------------------------------------

Do you "fall back" from daylight saving time to standard time
in the autumn?  What happens to timestamps in the shift period?
Unless you use timestamp with time zone, you will have a range
of time when the timestamp is ambiguous -- data has been lost.
If it is lost before it gets to you, I guess there's not much you can
do to restore it, unless you have a steady enough stream of
transactions at the boundry to notice when the timestamps go
back by about an hour.

Personally, I would probably use the format which can accurately
record the values, in hopes that the source would someday stop
losing the information.  It might save a table conversion later.

-Kevin


>>> Roland Walter <rwa@mosaic-ag.com> 10/13/05 9:20 AM >>>
Kevin Grittner schrieb:

...
>
> I definitely would not use timestamp without time zone even
> within a single time zone unless that time zone didn't use
> daylight saving time, or I was very confident that I didn't need
> to record times in the wee hours on days when the time
> shifted.  It's always safer to use timestamp with time zone
> when what you want to record is an instant in time.
>
> The only use case I've been able to think of where timestamp
> without time zone is semantically correct is where a publisher
> sets a release for, say, the next Harry Potter book.  Having
> attended a late night book release party this summer so that
> my kids could pick up their book as soon as 12:01 a.m. rolled
> into our time zone, I can see at least one use case for this
> data type.
>

I have one. I get timestamps without timezone in a CSV-file
with exported data of transactions from a so called 'Clearing-Center'
that converts EDIFACT-Messages. This data gets imported into the
database of the system that creates the bills. I can not provide
information that I do not have. So there is no use for timestamps with
timezones.

Hi,

It´s possible log operations make by the Apache DBCP.?

Thanks.
Rafa

Here is the link to their site, I suggest you ask them

http://jakarta.apache.org/commons/dbcp/

Dave
On 13-Oct-05, at 11:35 AM, rafa wrote:

> Hi,
>
> It´s possible log operations make by the Apache DBCP.?
>
> Thanks.
> Rafa
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>