you should prefix timestamp strings with "timestamp", ie timestamp '2003-11-26 10:36:53'

that only applies to SQL, not to MIL and/or "plain" asci_io. Arjan, do you have to monettime module loaded both when loading the data and when checking the results (i.e., when running the ls or dir)? Stefan

Arjan Scherpenisse wrote:

...

Hi list,

I am importing some tab-separated dump data in which a field is a timestamp, formatted ala "2003-11-26 10:36:53". After importing (through make_persistent/load_data), the two timestamp BATs show up as:

[ "profile_track_history_date", "oid", nil, 4516232, 0, "clean", "disk", "pers", 0, 1 ]

[ "profile_track_history_serverdate", "oid", nil, 4516232, 0, "clean", "disk", "pers", 0, 1 ]

(having a 'nil' tail-type). I guess this is a minor bug; because the BATs are filled with timestamps properly.

regards, Arjan

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

Sorry for the delay, but finally, here's my analysis. The problem is the following: In MonetDB/MIL, the currectly available types are kept in a BAT called "adm_atomtbl" (resp. a separate "monet_atomtbl" for each client context): ======== 11:38:54 manegold@draco:~ $ Mserver # Monet Database Server V4.3.16_rc06 # Copyright (c) 1993-2004, CWI. All rights reserved. # Compiled for i686-pc-linux-gnu/32bit; dynamically linked. # Visit http://monetdb.cwi.nl for further information. monet>adm_atomtbl.print; #-------------------------# # h adm_atomtbl # name # str int # type #-------------------------# [ "void", 0 ] [ "bit", 1 ] [ "chr", 2 ] [ "sht", 3 ] [ "bat", 4 ] [ "BAT", 4 ] [ "int", 5 ] [ "oid", 6 ] [ "ptr", 7 ] [ "flt", 8 ] [ "dbl", 9 ] [ "lng", 10 ] [ "str", 11 ] [ "uchr", 12 ] ======== When loading modules that provide new atoms, these are added to adm_atomtbl (and/or monet_atomtbl) in the order the modules are loaded: ======== monet>module(url); monet>adm_atomtbl.print; #-------------------------# # h adm_atomtbl # name # str int # type #-------------------------# [ "void", 0 ] [ "bit", 1 ] [ "chr", 2 ] [ "sht", 3 ] [ "bat", 4 ] [ "BAT", 4 ] [ "int", 5 ] [ "oid", 6 ] [ "ptr", 7 ] [ "flt", 8 ] [ "dbl", 9 ] [ "lng", 10 ] [ "str", 11 ] [ "uchr", 12 ] [ "url", 13 ] monet>module(monettime); monet>adm_atomtbl.print; #---------------------------------# # h adm_atomtbl # name # str int # type #---------------------------------# [ "void", 0 ] [ "bit", 1 ] [ "chr", 2 ] [ "sht", 3 ] [ "bat", 4 ] [ "BAT", 4 ] [ "int", 5 ] [ "oid", 6 ] [ "ptr", 7 ] [ "flt", 8 ] [ "dbl", 9 ] [ "lng", 10 ] [ "str", 11 ] [ "uchr", 12 ] [ "url", 13 ] [ "rule", 18 ] [ "tzone", 17 ] [ "timestamp", 16 ] [ "daytime", 15 ] [ "date", 14 ] ======== If we now create a BAT using the new atoms, the BAT descriptor of course stores the information about the types used for head and tail. I have to admit, I'm not too familiar with the details, but I guess, both the number and the name are stored. The number alone would not be enough, as with a subsequent session, modules might be loaded in different ordern and hence, the dynamic atom types get a different number. That's what happens in Arjans case, but let's redo it step by step: Create a persistent BAT using atom types from module monettime (which was loaded after module url was loaded, see above): ======== monet>a:=new(date,daytime).rename("DD").persists(true); monet>DD.print; #-------------------------# # h DD # name # date daytime # type #-------------------------# monet>ls; #---------------------------------------------------------------------------------------------------------# # name htype ttype count heat dirty status kind refcnt lrefcnt # name # str str str lng int str str str int int # type #---------------------------------------------------------------------------------------------------------# [ "DD", "date", "daytime", 0, 0, "dirty", "load", "pers", 0, 2 ] ======== Now, quit Mserver, and restart it: ======== monet>commit; monet>quit; 11:41:28 manegold@draco:/tmp/warehouse $ Mserver # Monet Database Server V4.3.16_rc06 # Copyright (c) 1993-2004, CWI. All rights reserved. # Compiled for i686-pc-linux-gnu/32bit; dynamically linked. # Visit http://monetdb.cwi.nl for further information. monet>adm_atomtbl.print; #-------------------------# # h adm_atomtbl # name # str int # type #-------------------------# [ "void", 0 ] [ "bit", 1 ] [ "chr", 2 ] [ "sht", 3 ] [ "bat", 4 ] [ "BAT", 4 ] [ "int", 5 ] [ "oid", 6 ] [ "ptr", 7 ] [ "flt", 8 ] [ "dbl", 9 ] [ "lng", 10 ] [ "str", 11 ] [ "uchr", 12 ] ======== no monettime loaded, yet. let's check the persistent BATs: ======== monet>ls; #-------------------------------------------------------------------------------------------------# # name htype ttype count heat dirty status kind refcnt lrefcnt # name # str str str lng int str str str int int # type #-------------------------------------------------------------------------------------------------# [ "DD", nil, nil, 0, 0, "clean", "disk", "pers", 0, 1 ] monet>DD.print; !ERROR: atom 'date' unknown, in BAT '28'. !ERROR: interpret: MIL operator invocation of 'print(BAT)' with illegal types. ======== Obviously, nothing known about the monettime types, yet... Hence, we load monettime: ======== monet>module(monettime); monet>adm_atomtbl.print; #---------------------------------# # h adm_atomtbl # name # str int # type #---------------------------------# [ "void", 0 ] [ "bit", 1 ] [ "chr", 2 ] [ "sht", 3 ] [ "bat", 4 ] [ "BAT", 4 ] [ "int", 5 ] [ "oid", 6 ] [ "ptr", 7 ] [ "flt", 8 ] [ "dbl", 9 ] [ "lng", 10 ] [ "str", 11 ] [ "uchr", 12 ] [ "rule", 17 ] [ "tzone", 16 ] [ "timestamp", 15 ] [ "daytime", 14 ] [ "date", 13 ] ======== NOTE: the type ID's of the monettime types are now one less than before, since type "url" of module "url" is missing...! consequently: ======== monet>ls; #---------------------------------------------------------------------------------------------------------# # name htype ttype count heat dirty status kind refcnt lrefcnt # name # str str str lng int str str str int int # type #---------------------------------------------------------------------------------------------------------# [ "DD", nil, "timestamp", 0, 0, "clean", "disk", "pers", 1, 1 ] monet>DD.print; !ERROR: interpret: MIL operator invocation of 'print(BAT[date,daytime])' with illegal types. ======== Apparenly, ls looks only at the type number in the BAT descriptor, but not at the type name: "date" & "daytime" were "14" & "15" when the BAT was created; "15" is now "timestamp"; I have now idea, why the head is "nil" and not "daytime", now ...??? However, accessing the BAT descriptor with the "print" did check the names and update the info in the BAt descriptor: ======== monet>ls; #---------------------------------------------------------------------------------------------------------# # name htype ttype count heat dirty status kind refcnt lrefcnt # name # str str str lng int str str str int int # type #---------------------------------------------------------------------------------------------------------# [ "DD", "date", "daytime", 0, 0, "clean", "load", "pers", 1, 1 ] monet>DD.print; #-------------------------# # h DD # name # date daytime # type #-------------------------# ======== Well, that's how I see this problem. It's "inconvenient", but not really a bug. I'm not sure, whether there is a better way to do it. Maybe, Niels or Peter do know more about it... Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |