[SOLVED] Unix Date to string format in SELECT statement.

[Kurrel]

Hi everyone,

 

I am tasked with ripping from an MSSQL database to then insert into a MYSQL database.  In the process I want to display a date from each record on the page.  The date is currently stored a Unix time stamp and I need to display it in hours:minutes day/month/year.

 

Normally I'd use PHP to convert but I need to draw straight from the DB in as much detail as possible.  Are there MSSQL commands for converting UNIX dates to string format and, if so, how may I use them?

 

~Kurrel

[Tatj]

I found that using the NOW() function in the INSERT INTO myTable(dateTime) VALUES(NOW());

looks identical to:

 

$nowtime=date('Y-m-d h:i:s');

echo $nowtime;

 

which give the look of  2007-07-26 22:41:00

 

Hope that helps.

 

[Tatj]

A question from myself,

 

I am dealing with having to delete dated time from my MySql table.  The reason is, this particular table will contain consumer credit information that must be automatically deleted within 15 days (I prefer 8 days). Credit card companies are really sensitive about this.

 

I have tried:

"SELECT datediff(now(),'2007-24-18T00:00:00') from myTable"

and even tried to subtract the time() in unix-timestamp from the different of "today" and the database archived time.  Even if I were to get this to work, how can I

"DELETE FROM myTable where timeField='more than eight days ago'" ?

I'm having a hard time getting this to work.

 

Thanks,

Tatj

 

Modify:

 

$nowtime=date('Y-m-d h:i:s');  does not work in the SELECT* FROM myTable where timeField='$nowtime'  when I've used the NOW() function when inserting info into the table (even though both look identical).

 

Struggling

 

[Kurrel]

I have no control over the original MSSQL database.  I am having to pull from it, not insert into.  I tried to work the datediff statement into it.  It compiles but then stops at the first run through the foreach loop.

[Tatj]

I have the same issue with datediff().  Can't use it either.  I will experiment using just unix timestamp.  If this works, it's just a question of comparison operators.  Will write back on this.

 

Maybe I'm not understanding correctly.  When I use the NOW() function, I use it withing the php query, not the database admin. 

 

$queryOne="INSERT INTO myTable(dateTime) VALUES(NOW());

 

$resultTable=mysql_query($queryOne) or die('Coud not do: ' . mysql_error());

 

The rest of the code to retreive the info (that I use)

 

$select="SELECT* FROM myTable";

$resultTable2=mysql_query($select) or die('Query failed: ' . mysql_error());

 

while($line=mysql_fetch_array($resultTable,MYSQL_ASSOC))

{

      $dateTime=$line['dateTime'];//dateTime is the field name

      echo "<tr><td> Date and Time: " . $dateTime . "</td></tr>";

}

 

This works well for me.

 

~Tatj

 

 

[Tatj]

If I understand the problem, I have it figured out. 

 

When I use the NOW() function in the INSERT INTO myTable(x) VALUES('$x');, the field was set to dateTime. So when the data was retrieved it was already formated.  To be able to retrieve and delete after a week, the format to compare needed to be of same format of the field.

This is the code that I got working:

 

$lastWeek=time()-(7*24*60*60); /*figures time from week ago exactly*/

$formatted=date('Y-m-d h:i:s',$lastWeek); /*formats time for comparison*/

$select="delete from payment where dateTime<'$formatted'"; //(notice the single then double quotes)

/*comparison of where database time is less than (older) than last week*/

$query=mysql_query($select) or die('Could not do query');

[Kurrel]

While not quite the answer, what you posted ended up solving the problem by making me think a different way.  I've read up on some inner join functions and restarted the whole affair.  The premise of what I was doing was flawed, and the different format of joins means my data comes out as I need it, rather than needing to be further manipulated.

 

Thanks!