How do you handle Date and Time with LabVIEW and Postgres?

[arikb]

Hello,

Does anybody have any experience in dealing with Postgres? I am trying this out as an alternative to MySQL and postgres doesn't like the Date/Time  type of data that LabVIEW Database Module creates. Does anyone have any suggestions?

Thanks!

[Wiebe@CARYA]

"arikb" <x@no.email> wrote in message
news:1224191408513-793643@exchange.ni.com...
> Hello,&nbsp;Does anybody have any experience in dealing with Postgres? I
am trying this out as an alternative to MySQL and postgres doesn't like the
Date/Time&nbsp; type of data that LabVIEW Database Module creates. Does
anyone have any suggestions?&nbsp;Thanks!

Convert it to doubles. Or to a date time string. Or an U64.

Regards,

Wiebe.

[arikb]

thanks for your reply,

Are you suggesting that in postgres I create the column to be a double or do I pass a double, U64, etc as a timestamp?

Arik

[Wiebe@CARYA]

"arikb" <x@no.email> wrote in message
news:1224250812786-794053@exchange.ni.com...
> thanks for your reply, &nbsp;Are you suggesting that in postgres I create
the column to be a double or do I pass a double, U64, etc as a timestamp?
&nbsp;Arik

Yes. It's not very elegant, but most of the times, databases (any) and
LabVIEW timestamps just don't get along very well. You can figure it out. If
you know what postgre wants, you can make a VI that converts a timestamp to
a string that postgre want, but my experiance is that it can be difficult.
If I recall correctly, access for instance wants the data time in windows
format, so it depends on your system settings. My guess is they didn't do
that in postgre, since it's a terrible thing and open source projects are
usually more practical in that regard.

Regards,

Wiebe.

[arikb]

I got it! Thanks for your help. Converting it to double made the most sense since there is the 'convert to timestamp' tool and vice versa available.

Cheers,

Arik

[DFGray]

Be aware that you are potentially throwing away a lot of information by converting to a double.  The LabVIEW timestamp has 128 bits of resolution.  A double has 54.  Depending on where the decimal falls and whether your timestamp is absolute or relative, you will have more or less problems.  In general, a double will preserve timestamps to about the microsecond level.  This is good enough for most applications, but not good enough for things like multi-acquisition high-speed digitizer timestamps.

[gharris]

I use postgres with npgsql - a .net client interface.  It works great and it supports labview timestamps.  I haven't seen anything about how many bits are used by postgres for timestamps, but I'll bet that npgsql passes the whole thing thru.  No casting necessary.   Another nice thing about postgres and npgsql is that they support array datatypes now.   If you're still trying out postgres and are interested, I can help with the npgsql part. 

[mikeporter]

The thing to understand is that unlike other datatypes, there is no standardized binary representation for time and date information. Hence, all time and data values are actually passed to the database as strings.The database then converts the string representation into its internal binary form using an expected format template.

I would therefore expect that the problem is that the structure of the time/date string that gets passed to the database doesn't match what the database is expecting. For example, say the database is expecting a date string in the form of MM/DD/YYYY (i.e. 12/22/2008) but you are sending the data in the form DD/MM/YYYY (i.e. 22/12/2008) - as for as the database is concerned you just specified the 12th day of the 22nd month...

What errors are you getting?

Mike...