0

I want to make monthly sales from the sales table but i store date data using time() format

Example :

UserID | Product | Date
1      | Hot Dog | 1504363230
4      | Chicken | 1504695631
1      | Potato  | 1504761716
3      | Spinach | 1505003789

So, how can i create monthly sales from there without replace Date into date() ? Because it will take a long time if i have to change 300K row

What should i do ?

Select * FROM Sales WHERE UserID = UserID AND Date = ?

the output must be like this

UserID 1 do 2 transaction in the last month or UserID 1 do 3 transaction in this month

Zeeeeth
  • 11
  • 2
  • You can convert it on MySQL it self, and filtering on the same way, it will be faster that make it on another way. – Ulises Jun 20 '18 at 15:39
  • Possible duplicate of [How to convert timestamp to datetime in MySQL?](https://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql) – devlin carnate Jun 20 '18 at 15:46
  • Looks like you have tons of good answers. On a related note, consider storing your time stamp as a `DATETIME` instead. It's readable as a string and you can still do math. You also won't run into the max integer size problem – Cfreak Jun 20 '18 at 15:46
  • @devlincarnate sorry, I do not mean that – Zeeeeth Jun 20 '18 at 15:52

4 Answers4

0

A PHP solution would be to use the second parameter in the date function:

string date ( string $format [, int $timestamp = time() ] )

The optional timestamp parameter is an integer Unix timestamp that defaults to the current local time if a timestamp is not given. In other words, it defaults to the value of time().

Script47
  • 14,230
  • 4
  • 45
  • 66
0

EDIT:
I think this should work for you. I guess you want to avoid PHP's date() but MySQL's DATE() is okay.

SELECT
    COUNT(*) AS nb_transaction,
    UserID
FROM
    sales
WHERE
    DATE(`Date`) LIKE '2018-06-%'

Of course replace the month with the one you are looking for.

SQL (will be faster than PHP):
You can convert any UNIX Timestamp using FROM_UNIXTIME() to any format: 
FROM_UNIXTIME(`Date`, '%Y %D %M %h:%i:%s')

PHP:
The time() function returns a UNIX Timestamp.

If you want to display a date using one, you can use DateTime::setTimestamp:

$date = new DateTime();
$date->setTimestamp($row['Date']);
echo $date->format('Y-m-d H:i:s');    // Hot Dog = 2017-09-02 14:40:30

Or shortly:

echo (new DateTime())->setTimestamp($row['Date'])->format('Y-m-d H:i:s');
AymDev
  • 6,626
  • 4
  • 29
  • 52
0

You can convert a unix timestamp to a datetime string in MySQL with FROM_UNIXTIME.

SELECT FROM_UNIXTIME(`Date`) as `Datetime` FROM sales

You can then expand on this with other date functions like MONTH or YEAR

SELECT MONTH(FROM_UNIXTIME(`Date`)) as `Month` FROM sales
Devon Bessemer
  • 34,461
  • 9
  • 69
  • 95
-1
$timestamp = 1517317337107; $date=date('Y-m-d H:i:s',
   $timestamp/1000); 
 echo "date and time : ". $date."<br>";
   $month=date('Y-m-d',$timestamp/1000);
 echo "only date : ".$month."<br>";


#output
date and time : 2018-01-30 13:02:17
only date : 2018-01-30

hope this will help

Silpa
  • 61
  • 3
  • I don't like to downvote, but a solution with date was already posted and your dividing by 1000 would result in an invalid result based on the OP's inputs. – Devon Bessemer Jun 20 '18 at 15:55
  • thanks for the answer, but i don't mean so. Sorry, I've edited the post more clearly – Zeeeeth Jun 20 '18 at 16:16