InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples

CVS Repositories

Who We Are
News, Articles & Events
Getting Involved
Contact Us

Case Studies
On The Edge! -NEW-
Commercial Vendors

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

InstantDB: RE: InstantDB and Date columns


There's a couple of gotchas in IDB date processing to look out for. The main
one is that date columns are associated with the date format that was in
effect when they got created. i.e. You can't change the way a date gets
displayed once the table has been created.

I also notice that you set your date format to "yyyy-mm-dd hh:nn:ss.l". IDB
won't recognise this. You have to use "yyyy-mm-dd hh:nn:ss.lll" if you want
milliseconds to get included.

I've attached a very simple program that shows how to store timestamps in
IDB. It shows what happens when you create a date column with a full
timestamp format and then insert various values. It inserts one value in the
column's expected format, one in an alternative format (and illustrates how
dates can be ambiguous) and a couple of object based PreparedStatement
values. This example can't accept Time objects because it's expecting a date
to always be included.

Hope some of this helps. If you need more info, then please post to That way, others will get answers to some of these
questions as well.


Peter Hearty
Lutris Technologies UK

-----Original Message-----
From: Johan Compagner []
Sent: 07 November 2000 16:56
Subject: Re: InstantDb and Date columns

I tested it some more but what ever i do
setting a line in the PRP file:
dateFormat=yyyy-mm-dd hh:nn:ss.l
SET DATE FORMAT "yyyy-mm-dd hh:nn:ss.l"

AWT-EventQueue-0 insert into schema values ( 1 , 11 , '2000-11-07
16:43:00.0' , '')

does not insert the time portion.

And i read your doc:
Inserting a literal timestamp into a date column will result in the full
timestamp being inserted, regardless of the date format.
Time zone differences can also result in unexpected values in a date column.
That's exactly what i want!!
But it doesn't work so it must be a bug?

My locale is "nl","nl" (the netherlands)

The only whay for me to insert time into InstantDb is to use:
AWT-EventQueue-0 insert into schema values(2,2,'21-01-2000 19:00' ,'')

But i don't want to generate my own time stamp, i want to set objects (Date
or Timestamp)
and those are generating the wrong value:
insert into schema values ( 1 , 11 , 2000-11-07 16:43:00.0 , __-__ )

Johan Compagner

P.S. when will the source be released? Then i can look and fix this myself!!

----- Original Message -----
From: "Johan Compagner" <>
To: <>
Sent: Tuesday, November 07, 2000 5:11 PM
Subject: InstantDb and Date columns

> Hi,
> something is going wrong with date columns.
> I is set a Date (or TimeStamp) througth the preparedstatement:
> ps.setTimestamp(x, myTimeStamp)
> and i execute that ps.
> then i see this happening:
> AWT-EventQueue-0 insert into schema values ( 1 , 11 , 2000-11-07
16:43:00.0 , __-__ )
> But because of the different dateformat that InstantDb has.
> the time (hh:nn) is suddenly not inserted!!
> I tried things out setting the dateFormat property myself.
> and yes the time is only inserted when the dateformat property is exactly
the same.
> But can i be sure that when i provide the statement only a Date object
what kind of format
> PrepareStatement is generating for that date object???
> If i set a DATETIME column with a TimeStamp of Date object (so i don't
generate the insert statement myself)
> then regarless of what format is set i want the complete date (inc hh:nn)
inserted in the database
> (because it is a date TIME column)
> But if this can't be done easy then i propose another thing:
> When generating the date string from a date or timestamp object. The jdbc
driver/ statement class checks
> the dateformat string of the database and formats the Date/Time Object
> So that it always follows the right format string.
> For now i must set the dateformat string to yyyy-dd-mm
> But what happens if my program runs under a different locale??
> Will the same dateformat string be generated from the timestamp?
> please fix this.
> thx
> Johan Compagner

begin 666
M5"!$051%($9/4DU!5" G>7EY>2UM;2UD9"!H:#IN;CIS<RYL;&PG(BP-"@D)
M(D-214%412!404),12!M>71A8FQE("AI9"!I;G0L9&%T93$)9&%T92DB+ T*
M"0DB24Y315)4($E.5$\@;7ET86)L92!604Q515,@*#$L("<R,# P+3$Q+3@@
M4R H,BP@)S@M,3$M,C P," Q,CHX)RDB+ T*"0DB24Y315)4($E.5$\@;7ET
M86)L92!604Q515,@*#\L(#\I(BP-"@D)(E-%3$5#5" J($923TT@;7ET86)L
M92)].PT*#0H-"@EP=6)L:6,@<W1A=&EC('9O:60@;6%I;B H4W1R:6YG(&%R
M9W-;72D@>PT*"0ET<GD@>PT*"0D)0VQA<W,N9F]R3F%M92 H(F]R9RYE;FAY
M;B!C;VX@/2!$<FEV97)-86YA9V5R+F=E=$-O;FYE8W1I;VX@* T*"0D)"2)J
M<W%L(#T@<W%L<UMI73L-"@D)"0EI9B H<W%L+FEN9&5X3V8H)S\G*2 A/2 M
M,2D@>PT*"0D)"0E0<F5P87)E9%-T871E;65N="!P<R ](&-O;BYP<F5P87)E
M4W1A=&5M96YT("AS<6PI.PT*"0D)"0EL;VYG(&YO=R ]("AN97<@:F%V82YU
M.PT*"0D)"0EP<RYS9714:6UE<W1A;7 @*#(L(&YE=R!4:6UE<W1A;7 H;F]W
M*2D[#0H)"0D)"7!S+F5X96-U=&4@*"D[#0H)"0D)"7!S+G-E=$EN=" H,2P@
M"7)S+F1U;7 H*3L-"@D)"0D)"7)S+F-L;W-E*"D[#0H)"0D)"7T@+R\@:68-
M97@I('L-"@D)"65X+G!R:6YT4W1A8VM4<F%C92 H*3L-"@D)?0T*"7T-"@T*

To unsubscribe from this mailing list, send email to
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at