topic Converting a timestamp to a date in QlikView

Converting a timestamp to a date

Fri, 05 Nov 2010 15:46:24 GMT

Hi,

I didn't find out in the post a solution of this issue.

I have a file with a date in timestamp in seconds.

I'd like to display the date in format 'YYYY-MM-DD'.

How ?

Thanks for any help

JJJ

Converting a timestamp to a date

Fri, 05 Nov 2010 15:52:11 GMT

Hi,

I had the same problem. This worked for me:

LOAD ........... date(ceil ( DateField)) AS DateField;

May be, you need a format string.

Regards, Roland

Converting a timestamp to a date

boorgura — Fri, 05 Nov 2010 15:54:49 GMT

JJJ,

These 4 functions should help achieving any conversion:

timestamp#(), timestamp(), date#(), date().

Of course, they will be driven by the Format string codes.

Hope it helps.

Converting a timestamp to a date

hector — Fri, 05 Nov 2010 16:16:56 GMT

Hi, a better solution for this issue is the function dayname()

Load
now() as TimeStamp,
dayname(now()) as Day
autogenerate(1);

Rgds

Converting a timestamp to a date

yblake — Fri, 05 Nov 2010 16:27:45 GMT

Hello Jean Jacques,

To convert Unix Timestamp to QlikView dates, use the following :

timestamp(<UnixTimeStamp>/ 86400 + 25569) as TS,
date(floor(<UnixTimeStamp>/ 86400 + 25569)) as Date,
time(frac(<UnixTimeStamp>/ 86400 + 25569)) as Time,

Yves.

Converting a timestamp to a date

Fri, 05 Nov 2010 17:00:13 GMT

Thanks Guys for your effort

Yves'solution is the only working.

JJJ

Converting a timestamp to a date

hector — Fri, 05 Nov 2010 17:09:23 GMT

Hi, i've uploaded this example long time ago, did you check it?

http://community.qlik.com/media/p/109527.aspx

Rgds

Converting a timestamp to a date

Fri, 05 Nov 2010 19:52:50 GMT

Thanks Hector, I didn't seen before.

Very nice example to understand the conversion of unix date.

regards

JJJ

Converting a timestamp to a date

djallelsadki — Tue, 24 May 2011 15:57:21 GMT

it's working thanks

Re: Converting a timestamp to a date

alexmosq — Wed, 16 Jan 2013 09:53:09 GMT

Exactly what I was looking for. Thanks!

Re: Converting a timestamp to a date

Sat, 12 Jul 2014 00:32:06 GMT

Héctor, the website is not accessable any more

Re: Converting a timestamp to a date

marcelo_7 — Wed, 03 Jun 2015 09:48:50 GMT

I have a timestamp 1141146494. When I use the function FROM_UNIXTIME(Time) it returns 2006-02-28 18:08:14 but the timestamp solution you proposed returns 2006-02-28 17:08:14. What am I missing?

Update: it seems Online Conversion - Unix time conversion says the same thing your function does so I assume the SQL function is at fault. Thanks!

Re: Converting a timestamp to a date

perla_chirag — Mon, 26 Oct 2015 11:23:21 GMT

Thanks Yves!

Was looking for this conversion

Re: Converting a timestamp to a date

menta — Wed, 04 Nov 2015 08:43:27 GMT

May be the changing to legal time?