Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
uogaz079
New Contributor II

How to Convert a BigInt to Gregorian date

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

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

FechasBigInt_MYSQL.jpg

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. 

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to Convert a BigInt to Gregorian date

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

6 Replies

Re: How to Convert a BigInt to Gregorian date

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
uogaz079
New Contributor II

Re: How to Convert a BigInt to Gregorian date

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. 

MVP & Luminary
MVP & Luminary

Re: How to Convert a BigInt to Gregorian date

Those look like unix timestamps. Try this formula:

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

-Rob

uogaz079
New Contributor II

Re: How to Convert a BigInt to Gregorian date

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. 

MVP & Luminary
MVP & Luminary

Re: How to Convert a BigInt to Gregorian date

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

Highlighted
uogaz079
New Contributor II

Re: How to Convert a BigInt to Gregorian date

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,

Solution_BigInttoGregorianDate.jpg