January 2008 - users-list

Re: [MonetDB-users] subscribe
by Yuvaraj Athur Raghuvir 30 Jan '08

30 Jan '08

Hello, I am experimenting with in-memory row-based & column-based stores. For the row-based store I am using SQLite. For the column-based, I am proposing to use MonetDB. In this context, I would like to join the mailing list. I am working on Windows XP / 32 and would like to use MonetDB as a DLL. Regards, Yuva

1 0

[MonetDB-users] SQL Function to remove all alpha characters
by Venks 28 Jan '08

28 Jan '08

Hi, Does MonetDB has regular expression support in SQL? I am looking to remove all alpha characters in a column value and wondering if there is a better way to do so rather than removing one character at a time. Thanks, -Venks

3 2

[MonetDB-users] Crash during COPY INTO on --nightly=stable 2008-01-21
by Bob Ippolito 23 Jan '08

23 Jan '08

So here's the core dump for our ETL test with --nightly=stable. We have not yet tried to restart mserver5. Here's some stats on the data dirs: $ du -sh * 10G bat 4.0K box 5.6G core.6950 $ find bat|wc -l 777933 (box is an empty dir) Core was generated by `//mochi/bin/mserver5 --dbname=mochiads_stats --dbinit=include sql; --dbfarm=/da'. Program terminated with signal 6, Aborted. #0 0x00002b5e2eb77cab in raise () from /lib/libc.so.6 (gdb) bt #0 0x00002b5e2eb77cab in raise () from /lib/libc.so.6 #1 0x00002b5e2eb79660 in abort () from /lib/libc.so.6 #2 0x00002b5e2ebaf66b in ?? () from /lib/libc.so.6 #3 0x00002b5e2ebb6b23 in ?? () from /lib/libc.so.6 #4 0x00002b5e2ebba26c in free () from /lib/libc.so.6 #5 0x00002b5e2d18f63e in GDKfree (blk=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_utils.mx:1140 #6 0x00002b5e2d12bd2d in HEAPfree (h=0x1e7270d0) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_heap.mx:265 #7 0x00002b5e2d1d3a50 in BATfree (b=0x1e726f98) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_bat.mx:522 #8 0x00002b5e2d129d3e in BBPfree (b=0x1e726f98, calledFrom=0x2b5e2d2eb338 "BBPtrim") at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_bbp.mx:1748 #9 0x00002b5e2d12a841 in BBPtrim (memtarget=0, vmtarget=0) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_bbp.mx:2081 #10 0x00002b5e2d190d7d in GDKmemchk (memchk=1, vmchk=761245848) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_utils.mx:688 #11 0x00002b5e2d19166d in GDKvmalloc (size=707393416, maxsize=0x42801688, emergency=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_utils.mx:1306 #12 0x00002b5e2d191b30 in GDKmallocmax (size=<value optimized out>, maxsize=0x3439f438, emergency=0) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_utils.mx:1051 #13 0x00002b5e2d12c630 in HEAPalloc (h=0x3439f438, nitems=88424175, itemsize=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_heap.mx:127 #14 0x00002b5e2d115a02 in HASHnew (hp=0x1b26, tpe=6, size=21315311, mask=18446744073709551615) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_search.mx:289 #15 0x00002b5e2d116fc4 in BAThash (b=0x2aab2d943118, masksize=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_search.mx:383 #16 0x00002b5e2d253853 in BAThashjoin (l=0x190, r=0x2aab2d943118, estimate=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_relop.mx:426 #17 0x00002b5e2d2501aa in batjoin (l=0x2aab9c1f6128, r=0x2aabb04f49f8, estimate=9223372036854775808, swap=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_relop.mx:1157 #18 0x00002b5e2d25050b in BATjoin (l=0x1b26, r=0x2356, estimate=6) at /scratch/monet.GNU.64.64.d.29168/MonetDB/src/gdk/gdk_relop.mx:1168 #19 0x00002aaab14a5302 in ALGjoinPath (mb=<value optimized out>, stk=0x2aab56174f18, pci=0x245b2378) ---Type <return> to continue, or q <return> to quit--- at /scratch/monet.GNU.64.64.d.29168/MonetDB5/src/optimizer/opt_joinpath.mx:223 #20 0x00002b5e2c880504 in runMALsequence (cntxt=0x604980, mb=0x2aab697741b8, startpc=1, stoppc=0, stk=0x2aab56174f18, env=0x0, pcicaller=0x0) at /scratch/monet.GNU.64.64.d.29168/MonetDB5/src/mal/mal_interpreter.mx:1159 #21 0x00002b5e2c8821cb in callMAL (cntxt=0x604980, mb=0x2aab697741b8, env=0x42803fd8, argv=0x42803fb0, debug=0 '\0') at /scratch/monet.GNU.64.64.d.29168/MonetDB5/src/mal/mal_interpreter.mx:377 #22 0x00002aaab266d19c in SQLexecutePrepared (cntxt=0x604980, be=<value optimized out>, q=0x2aab1c3ca488) at /scratch/monet.GNU.64.64.d.29168/sql/src/backends/monet5/sql_scenario.mx:1188 #23 0x00002aaab266ed0d in SQLengineIntern (c=0x604980, be=0x2aab140554d8) at /scratch/monet.GNU.64.64.d.29168/sql/src/backends/monet5/sql_scenario.mx:1273 #24 0x00002b5e2c8a105d in runScenarioBody (c=0x604980) at /scratch/monet.GNU.64.64.d.29168/MonetDB5/src/mal/mal_scenario.mx:558 #25 0x00002b5e2c8a113a in runScenario (c=0x1b26) at /scratch/monet.GNU.64.64.d.29168/MonetDB5/src/mal/mal_scenario.mx:622 #26 0x00002b5e2c87c1d8 in MSserveClient (dummy=<value optimized out>) at /scratch/monet.GNU.64.64.d.29168/MonetDB5/src/mal/mal_session.mx:471 #27 0x00002b5e2df362a5 in start_thread () from /lib/libpthread.so.0 #28 0x00002b5e2ec1661d in clone () from /lib/libc.so.6 #29 0x0000000000000000 in ?? () (gdb) And this is the log of everything printed by mserver5: !WARNING: GDKlockHome: created directory /data/db/monetdb/mochiads_stats/ !WARNING: GDKlockHome: ignoring empty or invalid .gdk_lock. !WARNING: BBPdir: initializing BBP. # MonetDB server v5.2.3, based on kernel v1.20.1 # Serving database 'mochiads_stats' # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-2007 CWI, all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /mochi/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.20.3 loaded >*** glibc detected *** //mochi/bin/mserver5: free(): invalid pointer: 0x00002aae5199e008 *** ======= Backtrace: ========= /lib/libc.so.6[0x2b5e2ebb6b23] /lib/libc.so.6(cfree+0x8c)[0x2b5e2ebba26c] /mochi/lib/libbat.so.0(GDKfree+0x2e)[0x2b5e2d18f63e] /mochi/lib/libbat.so.0(HEAPfree+0xad)[0x2b5e2d12bd2d] /mochi/lib/libbat.so.0(BATfree+0x1f0)[0x2b5e2d1d3a50] /mochi/lib/libbat.so.0[0x2b5e2d129d3e] /mochi/lib/libbat.so.0(BBPtrim+0x851)[0x2b5e2d12a841] /mochi/lib/libbat.so.0[0x2b5e2d190d7d] /mochi/lib/libbat.so.0(GDKvmalloc+0xdd)[0x2b5e2d19166d] /mochi/lib/libbat.so.0(GDKmallocmax+0xf0)[0x2b5e2d191b30] /mochi/lib/libbat.so.0(HEAPalloc+0x170)[0x2b5e2d12c630] /mochi/lib/libbat.so.0(HASHnew+0x32)[0x2b5e2d115a02] /mochi/lib/libbat.so.0(BAThash+0x334)[0x2b5e2d116fc4] /mochi/lib/libbat.so.0(BAThashjoin+0x3193)[0x2b5e2d253853] /mochi/lib/libbat.so.0[0x2b5e2d2501aa] /mochi/lib/libbat.so.0(BATjoin+0xb)[0x2b5e2d25050b] /mochi/lib/MonetDB5/lib/lib_optimizer.so(ALGjoinPath+0x222)[0x2aaab14a5302] /mochi/lib/libmal.so.0[0x2b5e2c880504] /mochi/lib/libmal.so.0(callMAL+0x25b)[0x2b5e2c8821cb] /mochi/lib/MonetDB5/lib/lib_sql.so(SQLexecutePrepared+0x25c)[0x2aaab266d19c] /mochi/lib/MonetDB5/lib/lib_sql.so(SQLengineIntern+0x21d)[0x2aaab266ed0d] /mochi/lib/libmal.so.0(runScenarioBody+0x14d)[0x2b5e2c8a105d] /mochi/lib/libmal.so.0(runScenario+0x3a)[0x2b5e2c8a113a] /mochi/lib/libmal.so.0(MSserveClient+0x58)[0x2b5e2c87c1d8] /lib/libpthread.so.0[0x2b5e2df362a5] /lib/libc.so.6(clone+0x6d)[0x2b5e2ec1661d] ======= Memory map: ======== 00400000-00404000 r-xp 00000000 fe:00 2115695 /mochi/bin/mserver5 00604000-00605000 rw-p 00004000 fe:00 2115695 /mochi/bin/mserver5 00605000-3ffec000 rw-p 00605000 00:00 0 [heap] 40000000-40001000 ---p 40000000 00:00 0 40001000-40801000 rw-p 40001000 00:00 0 40801000-40802000 ---p 40801000 00:00 0 40802000-41002000 rw-p 40802000 00:00 0 41002000-41003000 ---p 41002000 00:00 0 41003000-41803000 rw-p 41003000 00:00 0 41803000-41804000 ---p 41803000 00:00 0 41804000-42004000 rw-p 41804000 00:00 0 42004000-42005000 ---p 42004000 00:00 0 42005000-42805000 rw-p 42005000 00:00 0 2aaaaabaf000-2aaaaabb3000 r-xp 00000000 fe:00 2085181 /mochi/lib/MonetDB5/lib/lib_mdb.so.0.0.0 2aaaaabb3000-2aaaaadb3000 ---p 00004000 fe:00 2085181 /mochi/lib/MonetDB5/lib/lib_mdb.so.0.0.0 2aaaaadb3000-2aaaaadb4000 rw-p 00004000 fe:00 2085181 /mochi/lib/MonetDB5/lib/lib_mdb.so.0.0.0 2aaaaadb4000-2aaaaadb6000 r-xp 00000000 fe:00 2084480 /mochi/lib/MonetDB5/lib/lib_xml.so.0.0.0 2aaaaadb6000-2aaaaafb5000 ---p 00002000 fe:00 2084480 /mochi/lib/MonetDB5/lib/lib_xml.so.0.0.0 2aaaaafb5000-2aaaaafb6000 rw-p 00001000 fe:00 2084480 /mochi/lib/MonetDB5/lib/lib_xml.so.0.0.0 2aaaaafbd000-2aaaab03e000 r-xp 00000000 fe:00 540697 /lib/libm-2.5.so 2aaaab03e000-2aaaab23d000 ---p 00081000 fe:00 540697 /lib/libm-2.5.so 2aaaab23d000-2aaaab23f000 rw-p 00080000 fe:00 540697 /lib/libm-2.5.so 2aaaab23f000-2aaaab24d000 r-xp 00000000 fe:00 2016109 /mochi/lib/libMapi.so.0.0.0 2aaaab24d000-2aaaab44c000 ---p 0000e000 fe:00 2016109 /mochi/lib/libMapi.so.0.0.0 2aaaab44c000-2aaaab44d000 rw-p 0000d000 fe:00 2016109 /mochi/lib/libMapi.so.0.0.0 2aaaab44d000-2aaaab44e000 r-xp 00000000 fe:00 2085120 /mochi/lib/MonetDB5/lib/lib_unix.so.0.0.0 2aaaab44e000-2aaaab64d000 ---p 00001000 fe:00 2085120 /mochi/lib/MonetDB5/lib/lib_unix.so.0.0.0 2aaaab64d000-2aaaab64e000 rw-p 00000000 fe:00 2085120 /mochi/lib/MonetDB5/lib/lib_unix.so.0.0.0 2aaaab64e000-2aaaab650000 r-xp 00000000 fe:00 2085099 /mochi/lib/MonetDB5/lib/lib_lock.so.0.0.0 2aaaab650000-2aaaab84f000 ---p 00002000 fe:00 2085099 /mochi/lib/MonetDB5/lib/lib_lock.so.0.0.0 2aaaab84f000-2aaaab850000 rw-p 00001000 fe:00 2085099 /mochi/lib/MonetDB5/lib/lib_lock.so.0.0.0 2aaaab850000-2aaaab851000 r-xp 00000000 fe:00 2085118 /mochi/lib/MonetDB5/lib/lib_sema.so.0.0.0 2aaaab851000-2aaaaba51000 ---p 00001000 fe:00 2085118 /mochi/lib/MonetDB5/lib/lib_sema.so.0.0.0 2aaaaba51000-2aaaaba52000 rw-p 00001000 fe:00 2085118 /mochi/lib/MonetDB5/lib/lib_sema.so.0.0.0 2aaaaba52000-2aaaaba57000 r-xp 00000000 fe:00 2085116

2 1

[MonetDB-users] Change schema for already existing tables
by Travis Brady 22 Jan '08

22 Jan '08

Hello, Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least. thank you, Travis Brady

5 5

[MonetDB-users] Problems with COPY INTO and CHAR(N) columns containing empty strings or all spaces
by Bob Ippolito 22 Jan '08

22 Jan '08

We've had problems with MonetDB SR5 using COPY INTO with data that may be the empty string or all spaces. We've been able to work around this by surrounding all of the values in our copy files with "quotes" (I read tablet.c to try and diagnose the issue and found that as a workaround). I'm not familiar enough with the source and build process yet to submit a patch. We are using '\t' as the separator and the fields that we've had problem with are CHAR(2) or CHAR(16) if that matters. -bob

2 3

[MonetDB-users] Optimal filesystem/platform choices for MonetDB?
by Bob Ippolito 22 Jan '08

22 Jan '08

We've started doing a big import of data into SR5 and I've noticed that it's creating *A LOT* of files. So far we've got about 620k files for 6.7 GB of bat. I expect that a full import should have at least 20 times what's imported so far. It seems that loading data is relatively slow compared to PostgreSQL (we're using COPY) and I'm guessing it's due to all of these files. Right now we're using ext3 on amd64 ubuntu linux. Should we be looking at other platforms? -bob

2 3

[MonetDB-users] Core dump on first connect by mclient in BUNinplace
by Bob Ippolito 22 Jan '08

22 Jan '08

So we've been testing out a big ETL and it crashed again, and now every time we start mserver5 it will seemingly start up ok but as soon as mclient connects it will crash. I've generated a core dump. The db farm is 3.6GB and the core dump is 15MB. I can send either to someone if they need it to look further into the issue. The platform is 2.6.20-15-generic (root@yellow) (gcc version 4.1.2 (Ubuntu 4.1.2-0ubuntu4)) #2 SMP on amd64. It's running an unmodified SR5 superball that we compiled ourselves. I tried looking at strace and the --debug option with all of the flags on but it didn't produce anything that looked very interesting. The backtrace is here: Core was generated by `//mochi/bin/mserver5 --dbname=mochiads_stats --dbinit=include sql; --dbfarm=/da'. Program terminated with signal 11, Segmentation fault. #0 0x00002b88a35bf285 in BUNinplace (b=0x12458e8, p=0x0, h=<value optimized out>, t=0x1246eb0, force=1 '\001') at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1555 1555 ATOMreplace(b->ttype, b->theap, BUNtloc(b, p), t); (gdb) bt #0 0x00002b88a35bf285 in BUNinplace (b=0x12458e8, p=0x0, h=<value optimized out>, t=0x1246eb0, force=1 '\001') at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1555 #1 0x00002b88a35bfb75 in void_inplace (b=0x12458e8, id=1, val=0x1245a20, force=1 '\001') at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1671 #2 0x00002b88a35bfc33 in void_replace_bat (b=0x12458e8, u=0x1246d08, force=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1688 #3 0x00002aaab26c9a5a in load_bat (sname=<value optimized out>, tname=<value optimized out>, bname=<value optimized out>, type=<value optimized out>, sz=<value optimized out>, cnt=0x1214400) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/bat/bat_storage.mx:364 #4 0x00002aaab26c9b83 in create_col (tr=<value optimized out>, c=0x1245878) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/bat/bat_storage.mx:456 #5 0x00002aaab26c44a9 in load_table (tr=0xd3dec8, s=0xd3df38, rid=149) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/store.mx:511 #6 0x00002aaab26c5523 in load_trans (tr=0xd3dec8, id=1128) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/store.mx:845 #7 0x00002aaab26c6ff4 in store_init (debug=<value optimized out>, store=<value optimized out>, logdir=<value optimized out>, dbname=<value optimized out>, stk=0) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/store.mx:1318 #8 0x00002aaab26aab79 in mvc_init (debug=0, store=store_bat, stk=0) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/server/sql_mvc.mx:246 #9 0x00002aaab2665c97 in SQLinit (c=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/backends/monet5/sql_scenario.mx:225 #10 0x00002b88a2c7d42a in initScenario (c=0x604980, s=0x2b88a2e934d0) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/mal/mal_scenario.mx:264 #11 0x00002b88a2c58d82 in MSscheduleClient (command=0xb0a1b8 "LIT", challenge=0x41000fd0 "KdhtXMmr29n", fin=0xb10270, fout=0xb03030) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/mal/mal_session.mx:342 #12 0x00002aaab0e78247 in doChallenge (cmd=<value optimized out>, in=<value optimized out>, out=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/modules/mal/mserver.mx:482 #13 0x00002aaab0e79036 in SERVERlistenThread (Sock=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/modules/mal/mserver.mx:564 #14 0x00002b88a43122a5 in start_thread () from /lib/libpthread.so.0 #15 0x00002b88a4ff261d in clone () from /lib/libc.so.6 #16 0x0000000000000000 in ?? () -bob

2 2

Re: [MonetDB-users] MonetDB/SQL 64-bit
by Martin Kersten 22 Jan '08

22 Jan '08

Stefan Manegold wrote: > Hi Peter, > > unfortunately, this still does not help us much --- at least not me (but I'm > everything but a Windows expert) ... > the 4 tutorial sessions on MonetDB website have been designed to uncover installation errors quickly and to get a first working session. Is tutorial 1 running? > I know, though, that on Windows both server & client need to be started via > the proper batch scripts (or by clicking on the respective icons) to ensure > evironment and paths are set correctly. > yes, the commands execute a script, which sets paths correctly. locate them to see what is required to properly run both. Especially, if you have manually moved folders around or changed permissions. > To help you any further, (sorry for repeating myself), we'd need to know > the exact error message, or at least the paths/names of DLLs that are not > found. > > Furthermoe, could you please continue mailing to neten(a)cwi.nl (as requested > monet(a)cwi.nl > by the "Rply-To:" in the mail header of bot my first and this reply; or even > better to Monetdb-users(a)lists.sourceforge.net)? --- thus, more people than > just me get to see your problem and can help answering and solving it... > > Stefan > > On Tue, Jan 22, 2008 at 12:51:19AM -0600, Peter Haluska wrote: > >> Hi Stefan, >> >> installation has worked. It looks like that it is having problems with >> system paths while trying to run server file(can not find dlls) or server >> batch file. Thanks. >> >> -Peter >> >> >> >> On Jan 21, 2008 6:24 PM, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: >> >> >>> Dear Peter, >>> >>> thank you very much for your interest in MonetDB! >>> >>> To be of any help for you, it is necessary for us to know exactly what >>> does >>> (not) work for you. >>> >>> Does it install? >>> Can you start a server? >>> Can you start a client and connect to the server? >>> Can you load data? >>> Can you run a query? >>> >>> Hence, please let us know exactly which steps you took to download, >>> install, >>> start, etc. MonetDB/SQL, where you get stuck, whether you get any error >>> message(s), and if so, which one(s)... >>> >>> Kind regards, >>> Stefan >>> >>> >>> On Mon, Jan 21, 2008 at 06:02:05PM -0600, Peter Haluska wrote: >>> >>>> Hi, we are testing your software MonetDB/SQL >>>> 64-bit< >>>> >>> http://monetdb.cwi.nl/projects/monetdb//download.php?target=http://prdownlo… >>> >>> > on >>> >>>> our new system 'Windows Server 2008 RC1 Enterprise Edition' with 64-bit >>>> support server and we can not make it to work. >>>> Any suggestions? Thanks. >>>> >>>> -Peter >>>> > >

1 0

[MonetDB-users] Unexpected results with COALESCE and aggregates
by Bob Ippolito 21 Jan '08

21 Jan '08

MonetDB SR5 behaves unexpectedly when we've tried to use COALESCE with aggregates, here's an example: sql>CREATE TABLE tmp(a int); sql>SELECT SUM(a) FROM tmp; +-------+ | sum_a | +=======+ | 0 | +-------+ sql>SELECT MIN(a) FROM tmp; +-------------+ | min_a | +=============+ | null | +-------------+ sql>SELECT COALESCE(MIN(a), 0) FROM tmp; sql> For the first SELECT I would expect NULL, the second SELECT is okay, but I expect a result for the third! Here's what PosgreSQL does: bob=# CREATE TABLE tmp(a int); CREATE TABLE bob=# SELECT SUM(A) FROM tmp; sum ----- (1 row) bob=# SELECT MIN(a) FROM tmp; min ----- (1 row) bob=# SELECT COALESCE(MIN(a), 0) FROM tmp; coalesce ---------- 0 -bob

2 2

[MonetDB-users] timestamptz issues with non-GMT
by Bob Ippolito 21 Jan '08

21 Jan '08

We've been playing around with MonetDB-Mars-SuperBall-SR5 for the past couple weeks and we've been having some issues with timestamps. We unfortunately don't do all of our calculations on GMT so we've hit a couple walls because it seems that the MonetDB 5 mtime module doesn't consider timezones: (Yes I know the compiler seems to optimize out the original type if you look at the EXPLAIN output, but this happens for timestamptz coming from tables too) sql>set time zone interval '-08:00' hour to minute; sql>SELECT CAST(TIMESTAMPTZ '2008-01-01 00:00+00:00' AS DATE); +--------------+ | single_value | +==============+ | 2008-01-01 | +--------------+ PostgreSQL has the expected behavior, which accounts for the local time zone when converting timestamps to dates. bob=# SET TIMEZONE TO 'PST8PST8'; SET bob=# SELECT '2008-01-01 00:00+00:00'::timestamptz::date; date ------------ 2007-12-31 (1 row) This is the best I've been able to do, but it's of course ugly and it seems like it could break if the implementation gets fixed: sql>SELECT CAST(TIMESTAMPTZ '2008-01-01 00:00+00' + CURRENT_TIMEZONE AS DATE); +----------------------+ | sql_add_single_value | +======================+ | 2007-12-31 | +----------------------+ Is the "best" solution to just use the GMT timezone (or the TIMESTAMP type) and hardcode our timezone offset everywhere? sql>SELECT CAST(TIMESTAMP '2008-01-01 00:00' - SEC_INTERVAL '28800' AS DATE); +----------------------+ | sql_sub_single_value | +======================+ | 2007-12-31 | +----------------------+ -bob

1 0