Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, December 15, 2015 5:37 AM
Hi,
I have a json file and i'm using $hash command to pull out the values from json file, following the instructions from this link below:
My point is...
On json file, theres a date/time value in "CREATEDTIME":
{"request":{"WORKORDERID":"3","CREATEDTIME":"1450035713020","REQUESTER":"Guest"}}
this date/time value is based on database standard.
and i need to change format to "hh:mm dd/mm/yyyy".
Currently, my script is:
$hash = $jsondata -replace '{|}|"' -replace ",","`n" -replace ":","=" | ConvertFrom-StringData
$workorderid = $hash["workorderid"]
$createdtime = $hash["createdtime"]
$requester= $hash["requester"]
This script work very well, but i don't know how to convert the variable $createdtime to hh:mm dd/mm/yyyy date-time format.
I never worked w/ $hash before.
How can i do that?
Thanks for your support
All replies (14)
Tuesday, December 15, 2015 7:34 AM ✅Answered | 1 vote
Then this will work:
([datetime]'1/1/1970').AddMilliseconds(1450035713020)
Which produces:
Sunday, December 13, 2015 7:41:53 PM
Your instructions say the number of Seconds. THe epoch designation usese milliseconds. Your instructions are wrong but this will work.
This is also not an SQS timestamp field. It is a Unix format timestamp which is also referred to as an Internet timestamp.
\(ツ)_/
Tuesday, December 15, 2015 5:50 AM | 1 vote
We don't use hashes with json
$obj=ConvertFrom-Json '{"request":{"WORKORDERID":"3","CREATEDTIME":"1450035713020","REQUESTER":"Guest"}}'
$obj.request.CREATEDTIME
The time is not in time format. Without knowing the source it is not possible to know how to convert
It is not file time:
[datetime]::FromFileTime($obj.request.CREATEDTIME)
\(ツ)_/
Tuesday, December 15, 2015 6:06 AM | 1 vote
i get a date with this, but only you would know if it is correct. (borrowed code from jrv)
$obj=ConvertFrom-Json '{"request":{"WORKORDERID":"3","CREATEDTIME":"1450035713020","REQUESTER":"Guest"}}'
Function Convert-UnidentifiedDateTime ($weirdvalue) {
[timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($weirdvalue))
}
Convert-UnidentifiedDateTime -weirdvalue ([int]($obj.request.CREATEDTIME / 1000))
[>_]
Tuesday, December 15, 2015 6:09 AM
Jrv,
**$hash **is used to bring values from json file as you can see in this link below:
https://social.technet.microsoft.com/Forums/windowsserver/en-US/b91e74b5-4521-4696-aeca-71e3f13eaaa6/get-json-data-values?forum=winserverpowershell
The "CREATEDTIME" value was extracted from MSSQL Server. I think that is a MSSQL date/time format default/standard.
$hash work's very well.
But i need convert value from "CREATEDTIME".
Thanks
Tuesday, December 15, 2015 6:12 AM
With json you do not need a hash. Your code is from 5+ years ago. PowerShell now has json utilities. Use them as they will not break as easily.
The time, if from SQLServer is a timestamp. It is an internal representation that cannot be easily converted.
\(ツ)_/
Tuesday, December 15, 2015 6:18 AM
here is another reference to sql time conversion
[>_]
Tuesday, December 15, 2015 6:26 AM
That is for Unix TS usually from Oracle. The issue with a TIMESTAMP in this format is that it also contains a row version which can make it hard to convert. If you need convertible timestamps they need to be declared as [datetime] and not [timestamp]
I am not convinced that this is a SQLServer timestamp.
\(ツ)_/
Tuesday, December 15, 2015 6:56 AM
I found an internet information about timestamp in my ticket managment tool:
".....All Time stamps are stored in the unixtime format (number of seconds since 01-01-1970 00:00)."
Tuesday, December 15, 2015 6:56 AM
It is not a legitimate timestamp value for SQLServer 2008. It is an internal timestamp or it has been extracted incorrectly when it was extracted to Jason. Look at the table schema to see how it is defined. That is likely the only way we will find what that number is.
\(ツ)_/
Tuesday, December 15, 2015 7:01 AM
The suggestion that the date is offset from 1970 is also wrong as this number produces this error:
PS C:\scripts> [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($weirdvalue))
Exception calling "AddSeconds" with "1" argument(s): "Value to add was out of range.
Parameter name: value"
At line:1 char:1
- [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($weird ...
-
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentOutOfRangeException
\(ツ)_/
Tuesday, December 15, 2015 7:19 AM
I found an internet information about timestamp in my ticket managment tool:
".....All Time stamps are stored in the unixtime format (number of seconds since 01-01-1970 00:00)."
If that is true then you need to convert like this:
Extract out the number of days, minutes and seconds and create a timespan with that then add it to the base date.
\(ツ)_/
Tuesday, December 15, 2015 7:20 AM
The suggestion is correct. You can check in this link below:
http://www.epochconverter.com/
Just copy and paste the "CREATEDTIME" below value on URL above.
"CREATEDTIME":"1450035713020"
Thanks for attention
Tuesday, December 15, 2015 7:45 AM
Jrv,
Thank you so much. Exaclty what i need.
Thank you again
Tuesday, December 15, 2015 7:47 AM | 1 vote
Note that this is referred to as nano time. because the number after the decimal point is the number of nanosesonds.
This number1450035713020 is a truncated version of this: 1450035713020.00000
Here is some background on nanotime: https://blogs.oracle.com/dholmes/entry/inside_the_hotspot_vm_clocks
\(ツ)_/