Jump to content

php unix timestamp conversion


GaneshVenkatesh

Recommended Posts

 
 

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

 

Link to comment
Share on other sites

 

 
 

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

Link to comment
Share on other sites

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() ";
Link to comment
Share on other sites

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           |
+-------------------------------+

Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.