php unix timestamp conversion

[GaneshVenkatesh]

 

 

I have column in DB with logtime and format looks like this(example is 001367596058040),,,,,i did not understand the appended 2 zeros at start for tht logtime data and it is making difficult to compare

I need to compare user selected date with date in DB,,,date in DB is in 15 digit Unix format.. I have dropdown where user can select data for yesterday,last2 days and last week...I have issue with comparing the dates user selects with tht in DB,,,

$newdate = strtotime ( '-1 day' ) * 1000 ;

$newdate1 = strtotime ( '-2 day' ) * 1000 ;

$newdate2 = strtotime ( '-7 day' ) * 1000 ;

at this time $newdate gives 1384734321000,,,,,Please help to get $newdate to be in the format in DB so tht I can sort them as per selected times

 

[GaneshVenkatesh]

 

 

 

I have column in DB with logtime and format looks like this(example is 001367596058040),,,,,i did not understand the appended 2 zeros at start for tht logtime data and it is making difficult to compare

I need to compare user selected date with date in DB,,,date in DB is in 15 digit Unix format.. I have dropdown where user can select data for yesterday,last2 days and last week...I have issue with comparing the dates user selects with tht in DB,,,

$newdate = strtotime ( '-1 day' ) * 1000 ;

$newdate1 = strtotime ( '-2 day' ) * 1000 ;

$newdate2 = strtotime ( '-7 day' ) * 1000 ;

at this time $newdate gives 1384734321000,,,,,Please help to get $newdate to be in the format in DB so tht I can sort them as per selected times

 

can somebody please check question

[Barand]

Are you storing logtime in a varchar() column instead of int? You wouldn't get the leading zeros in a numeric type field.

 

It's a lot easier to store dates as type DATE (format yyyy-mm-dd). That's why it's there. Then it's just

$days = 2;  // or 1 or 7
$sql = "SELECT whatever FROM tablename WHERE logtime BETWEEN CURDATE() - INTERVAL $days DAY AND CURDATE() ";

You can still do a similar query but you now have to convert your unix date to a real date first (every record every query using logtime comparisons)

$days = 2;  // or 1 or 7
$sql = "SELECT whatever FROM tablename WHERE FROM_UNIXTIME(logtime) BETWEEN CURDATE() - INTERVAL $days DAY AND CURDATE() ";

[GaneshVenkatesh]

Yes the logtime was stored as 15 digit type char,,, and I am not able to get date in tht format to compare,, any ideas?

[Barand]

You would have more success if the logtimes were valid unix time values - they are 3 digits too long

 

The value in your example (001367596058040) is valid if the final 040 is removed

mysql> SELECT FROM_UNIXTIME('001367596058040');
+----------------------------------+
| FROM_UNIXTIME('001367596058040') |
+----------------------------------+
| NULL                             |
+----------------------------------+


mysql> SELECT FROM_UNIXTIME('001367596058');
+-------------------------------+
| FROM_UNIXTIME('001367596058') |
+-------------------------------+
| 2013-05-03 16:47:38           |
+-------------------------------+

[dalecosp]

15 digit Unix format

 

The current UNIX time is only 10 digits; as Barand observes, you have too many digits.

 

Why are you doing this?

$newdate2 = strtotime ( '-7 day' ) * 1000 ;

That's where your extra digits are coming from.  strtotime() returns a valid UNIX timestamp (well, usually) --- you don't need to change it, multiply it, etc.

Edited November 19, 2013 by dalecosp

[dalecosp]

Your DB is Oracle or MS or something that's padding the front-end of the Unix Timestamp.

 

When you get the TS from the DB, do it something like this:

 

select trim(leading '00' from $number_field) as $mynumber;

[GaneshVenkatesh]

Select column name from table where logtime> date,,, logtime is db field I mentioned above,,, I need to get the date in format of logtime,,,, in my form user can select 1day, 2 days, last week,,, I need to get these 3 in format of logtime

[Barand]

Well, if you are storing the datetime in microseconds then your code should be OK

$newdate2 = strtotime ( '-7 day' ) * 1000 ;

then

$sql = "SELECT logtime FROM tablename WHERE logtime BETWEEN $newdate2 AND UNIX_TIMESTAMP()*1000";

Edited November 19, 2013 by Barand