Can you decipher this timestamp?

[cate]

I'm using a webservice to update a metals ws. Anyone know how to
decode the timestamp?

<pricelist currency="usd">
<price timestamp="1354524720" per="ozt" commodity="gold">1718.35</
price>
<price timestamp="1354524720" per="ozt"
commodity="palladium">683.00</price>
<price timestamp="1354524720" per="ozt"
commodity="platinum">1605.00</price>
<price timestamp="1354524720" per="ozt" commodity="silver">33.61</
price>
</pricelist>

Thanks in advance

 

[Claus Busch]

Hi,

Am Mon, 3 Dec 2012 07:37:39 -0800 (PST) schrieb cate:

I'm using a webservice to update a metals ws. Anyone know how to
decode the timestamp?

<pricelist currency="usd">
<price timestamp="1354524720" per="ozt" commodity="gold">1718.35</
price>

that is a unix time stamp. Look in web for Unix Timestamp Converter.
354524720 is 12-03-2012 16:48:04 UTC

Regards
Claus Busch

 

[joeu2004]

Am Mon, 3 Dec 2012 07:37:39 -0800 (PST) schrieb cate:

I'm using a webservice to update a metals ws.
Anyone know how to decode the timestamp?
<price timestamp="1354524720" [....]

that is a unix time stamp. Look in web for
Unix Timestamp Converter.
354524720 is 12-03-2012 16:48:04 UTC

If Claus is correct (and I would guess he is), you can do the conversion in
Excel using:

=--TEXT(354524720/86400 + DATE(1970,1,1),"m/d/yyyy hh:mm:ss")

and in VBA using:

Dim x as Double
x = CDate(Format(354524720/86400 + DateSerial(1970,1,1),"m/d/yyyy
hh:mm:ss"))

where "m/d/yyyy" should be the short-date form configured in the Regional
and Language Options control panel (WinXP).

(I prefer not to use type Date because VBA's special interpretation often
screws me up.)

However, that returns 12/3/2012 08:52:00, not 16:48:04.

I believe 08:52:00 is the correct result, since "Unix time" is the number of
seconds since 1/1/1970 midnight (00:00) "not counting leap seconds". The
formulas above agree with the dynamic example at
http://en.wikipedia.org/wiki/Unix_time.

 

[Claus Busch]

Hi Joe,

Am Mon, 3 Dec 2012 08:33:28 -0800 schrieb joeu2004:

However, that returns 12/3/2012 08:52:00, not 16:48:04.

I believe 08:52:00 is the correct result, since "Unix time" is the number of
seconds since 1/1/1970 midnight (00:00) "not counting leap seconds". The
formulas above agree with the dynamic example at
http://en.wikipedia.org/wiki/Unix_time.

your are right. I have a makro to convert Unix time into GMT and I get
03.12.2012 09:52:00
But is it so simple to divide the unix time by 86400 and add the unix
start date? I think that PST and PDT must be considered


Regards
Claus Busch

 

[joeu2004]

But is it so simple to divide the unix time by 86400
and add the unix start date? I think that PST and PDT
must be considered

There was no information to indicate whether the timestamp is local time or
GMT.

If the timestamp is GMT and "cate" wants local time, yes, some timezone
offset must be added or subtracted. But the same would be true of any
representation of GMT in the file.

On the other hand, if the timestamp is already local time, no timezone
offset is needed unless "cate" wants to convert it to GMT.