Can anybody tell me what time format is this 635151121709530000 and how to convert, the result is something like 2013-9-18 14:42:50
Thanks
There's not enough info here to convert your number to a time. There are at least two common ways to represent a point in time:
The first (and most common) is that the number represents a count of units of time between the time represented by the timestamp and some "epoch" date. In order to convert it to a date, you'd need two pieces of info: the scale (the amount of time that elapses between the timestamps N and N+1), and a reference timestamp (commonly the epoch date, which is the date/time that the number 0 represents).
Without that info, you would need at least two timestamps, and the date/time each represents. You could then find the difference between them, divide by the number of seconds, and that'll give you the scale. You could then extrapolate to find the epoch date.
The second possibility is that the number is a bit field, where each part of the date is represented by a certain set of bits in the integer.
If that's the case, then you're somewhat screwed; there could be all kinds of math going on to fit the date parts into a bit field. You will have to either ask someone in charge of the system, or if that's you now, examine the code that generates timestamps.
Edit: If we take the numbers you provided in the comments:
635155956336800000 2013-9-24 05:00:33
635155957368670000 2013-9-24 05:02:16
635155968617830000 2013-9-24 05:21:10
635155968728200000 2013-9-24 05:21:12
635155971811300000 2013-9-24 05:26:21
635155972798800000 2013-9-24 05:27:59
635155978347870000 2013-9-24 05:37:14
Let's see if the numbers make sense as counts.
Take the first and last times and find the difference between their respective timestamps.
635155978347870000 - 635155956336800000 = 22011070000 ticks
Divide by the number of seconds separating the two times ((37*60+14) - 33 == 2201 seconds)
22011070000 ticks / 2201 sec = 10000486.142662 ticks/sec
Round to a nice even number, cause timestamps are based on useful intervals unless you're obfuscating. So in your case, it looks like the scale is 10 million ticks per second, or 100ns per tick.
Verify with the other timestamps (in this case, the 5:27:59 one and the 5:00:33):
635155972798800000 - 635155956336800000 == 16462000000 ticks
16462000000 ticks / 10000000 ticks/sec = 1646.2 sec
(27*60+59) - 33 == 1646 sec
So 100ns looks about right. In order to be sure, though, we'd need timestamps further apart. Just to make sure this is indeed a count rather than, say, two 31-bit numbers representing the year and the current second of that year.
Assuming this is a count, if we take the first timestamp and divide by 10 million:
635155956336800000 ticks / 10000000 ticks/sec = 63515595633.6800000 sec
That's 63.5 billion seconds since the epoch. For reference, 32-bit signed timestamps (which number a bit over 2 billion) will run out in 2038, 68 years from the Unix epoch (1970). This number is about ~29.58 times that, or 29.58 * 68 ~= 2011 years.
That'd put the epoch around the beginning of the first century CE. 1 CE is a significant enough date that it'd make sense as the epoch date. I don't feel like doing the math to confirm that, though.
Corresponding with cHao's 10000000 ticks per second, but using a base date of 01/01/0001 as I'd originally suggested:
$values = array(
array(635155956336800000, "2013-9-24 05:00:33"),
array(635155957368670000, "2013-9-24 05:02:16"),
array(635155968617830000, "2013-9-24 05:21:10"),
array(635155968728200000, "2013-9-24 05:21:12"),
array(635155971811300000, "2013-9-24 05:26:21"),
array(635155972798800000, "2013-9-24 05:27:59"),
array(635155978347870000, "2013-9-24 05:37:14"),
);
function convert($date) {
static $monthArray = array(
31,29,31,30,31,30,31,31,30,31,30,31
);
$date = $date / 10000000 / 24 / 60 / 60;
$y = floor($date / 365.2425);
$YYYY = $y + 1;
$z = 365 * $y + floor($y/4) - floor($y/100) + floor($y/400);
$d = $date - $z;
$month = 1;
while (true) {
$d -= $monthArray[$month - 1];
if ($d < 0) break;
$month++;
$dd = $d;
}
$hh = ($dd - floor($dd)) * 24;
$hours = floor($hh);
$mm = ($hh - floor($hh)) * 60;
$minutes = floor($mm);
$ss = ($mm - floor($mm)) * 60;
$seconds = floor($ss);
return sprintf('%04d-%02d-%02d %02d:%02d:%02d', $YYYY, $month, floor($dd), $hours, $minutes, $seconds);
}
foreach($values as $value) {
echo convert($value[0]), PHP_EOL;
}
gives:
2013-09-22 05:00:33
2013-09-22 05:02:16
2013-09-22 05:21:01
2013-09-22 05:21:12
2013-09-22 05:26:21
2013-09-22 05:27:59
2013-09-22 05:37:14
I'm not sure where the 2-day discrepancy comes from, but the output can probably arbitrarily have 2 days added and unless you're playing with extremely historic dates closer to the 0 timestamp mark, then it'd probably be a good enough function to use