Convert the Timestamp value to BigInt

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 11:03 am

Hi,

Can anyone please let me know how to convert the Timestamp value to BigInt. The reson is we are using the MySql as database but we need to store the milli seconds also.

For Ex: 2012-01-04 11:12:13:001 to 20120104111213001

Thanks.

pandeesh · Post by **pandeesh** » Wed Jan 04, 2012 1:06 pm

Try using convert() to remove the -,:and the space.
Something like below:

Code: Select all

Convert("-: ","",Timestamp)

If you still face any issue cover the above within AsInteger().

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 2:46 pm

while using the convert function, I am not getting the milli seconds into the result value.

For ex: Convert('-:. ','',2012-01-04 15:43:09.989564). I am getting the result as 20120104154309. But i need the result as 20120104154309989564. Please suggest.

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 2:47 pm

while using the convert function, I am not getting the milli seconds into the result value.

For ex: Convert('-:. ','',2012-01-04 15:43:09.989564). I am getting the result as 20120104154309. But i need the result as 20120104154309989564. Please suggest.

pandeesh · Post by **pandeesh** » Wed Jan 04, 2012 2:57 pm

Can you check whether your time stamp value has the milli seconds?

ray.wurlod · Post by **ray.wurlod** » Wed Jan 04, 2012 3:05 pm

Is the Scale value in the metadata set to 6?

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 3:53 pm

pandeesh wrote:Can you check whether your time stamp value has the milli seconds?

.

Yes. the timestamp value is having the milli seconds.

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 3:54 pm

ray.wurlod wrote:Is the Scale value in the metadata set to 6? ...

.

For the BigInt do we need to mentioned the scale value?

pandeesh · Post by **pandeesh** » Wed Jan 04, 2012 3:58 pm

No he asked about the timestamp data type.

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 4:24 pm

Ok.

The CurrentTimestampMS() function will return the dateTime along with milli seconds. ( ex: 2012-01-04 16:35:00.189089). I want to store this value as 20120104163500189089 in database ( field datatype is BigInt). Please let me know how i can implement this.

jwiles · Post by **jwiles** » Wed Jan 04, 2012 5:06 pm

You have been given a solution already within this thread using Convert() to remove the punctuation from your (character) timestamp. Once converted you simply place it into a BigInt column.

Keep in mind that the maximum value an unsigned BigInt (64-bit integer) can represent is 18,446,744,073,709,551,615. This is 20 digits and would equate to 1844-67-44 07:37:09.551615 when reformatted to YYYY-MM-DD HH:NN:SS.SSSSSS. A signed BigInt could represent a maximum (positive or negative) value of half the unsigned maximum value, 19 digits in length.

Regards,

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 8:58 pm

Thanks. But it is loading into the mysql database as "9223372036854775807" ( some unknown value). While populating into the with Varchar datatype is populating corretly, but the same value while populating into the BigInt column some unknown value is populating.

venkates.dw · Post by **venkates.dw** » Wed Jan 04, 2012 9:59 pm

Thanks. I am able to store the value in BigInt filed with 5 digit milli seconds.

jwiles · Post by **jwiles** » Thu Jan 05, 2012 12:12 am

That "some unknown value" is the maximum positive value that can be represented by a signed BigInt.
<a href="http://en.wikipedia.org/wiki/Integer_(c ... cience)</a>