GaneshVenkatesh Posted November 19, 2013 Share Posted November 19, 2013 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 Quote Link to comment Share on other sites More sharing options...
GaneshVenkatesh Posted November 19, 2013 Author Share Posted November 19, 2013 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 19, 2013 Share Posted November 19, 2013 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() "; Quote Link to comment Share on other sites More sharing options...
GaneshVenkatesh Posted November 19, 2013 Author Share Posted November 19, 2013 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted November 19, 2013 Share Posted November 19, 2013 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 | +-------------------------------+ Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 19, 2013 Share Posted November 19, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 19, 2013 Share Posted November 19, 2013 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; Quote Link to comment Share on other sites More sharing options...
GaneshVenkatesh Posted November 19, 2013 Author Share Posted November 19, 2013 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 19, 2013 Share Posted November 19, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.