Solved: Re: How to Convert a BigInt to Gregorian date - Qlik Community

Anonymous · ‎2017-10-17

I'm operating on QSense with a connection to a MYSQL data base.

In my data model, every date fileds are MYSQL BigInt.

In the QSense stage scripting I need to convert them to a gregorian date format.

Would you mind helping me to figure it out ?

Regards!

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Laboratorios Casasco S.A.I.C.

rwunderlich · ‎2017-10-18

Hi Ulises,

The expression I gave does just that, converts from unix format to gregorian in your default format. If you want to use an output format other than default, add the optional format mask to the TimeStamp() function:

TimeStamp(Makedate(1970,1,1)+ (unixtime/1000) /24/60/60, 'MMM DD, YYYY hh:mm')

-Rob

View solution in original post

Anil_Babu_Samineni · ‎2017-10-17

What was the format you are expecting?

For example, the International Organization for Standardization (ISO) format is YYYY-MM-DD. 2013-12-14 is equivalent to the calendar date December 14th 2013. Are you expecting similar that?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-10-17

I need to convert them to a date or datetime format easy to read. For example:

a) 17/10/17                             DD/MM/YY
b) 17/10/2017                         DD/MM/YYYY
c) 17-10-17 11:51                   DD/MM/YY HH:MM
d) Oct 17, 2017 11:51 AM      Month DD, YYY HH:MM AM

Thanks in advanced.

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Laboratorios Casasco S.A.I.C.

rwunderlich · ‎2017-10-17

Those look like unix timestamps. Try this formula:

TimeStamp(Makedate(1970,1,1)+ (unixtime/1000) /24/60/60)

-Rob

Anonymous · ‎2017-10-18

hi Rob,

you're right. But I neet to convert from Unix Timestamp to Gregorian format date.

For example: How to convert 1506623932679 to "28/09/2017 15:38" or "28/09/2017" or "Sep 28, 2017 03:38 PM" ?

Thanks in advanced

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Laboratorios Casasco S.A.I.C.

rwunderlich · ‎2017-10-18

Hi Ulises,

The expression I gave does just that, converts from unix format to gregorian in your default format. If you want to use an output format other than default, add the optional format mask to the TimeStamp() function:

TimeStamp(Makedate(1970,1,1)+ (unixtime/1000) /24/60/60, 'MMM DD, YYYY hh:mm')

-Rob

Anonymous · ‎2017-10-18

Thank you very much, Rob. Finally, I've get it.

Sintaxis:

TimeStamp(Makedate(1970,1,1)+(CREATEDTIME/1000) /24/60/60, 'MMM DD, YYYY hh:mm') as MiFechaTStampPrueba99,