0

I am generating a report of orders broken down by date, month and year using the aggregate group function. My date is stored in the MongoDate object which is in UTC.

'$group' => array (
    '_id' => array (

        'year' => array ( '$year' => '$created' ),
        'month' => array ( '$month' => '$created' ),
        'day' => array ( '$dayOfMonth' => '$created' ),

    )
)

I have tried adding to the date object using project, but it seems like I cannot add to a MongoDate Object. (As seen here: https://stackoverflow.com/a/18854229/1069277)

'$project' => array (

    'created' => array (

        '$add' => array (

            '$created' => 8 * 60 * 60 * 1000

        )

    )

)

Is adding another field that stores with timezone corrected the only way to achieve this at this moment?

Community
  • 1
  • 1
JC Lee
  • 2,337
  • 2
  • 18
  • 25

1 Answers1

0

I could go on about just using the date operators from aggregation and doing the date math in order to change it into a timestamp value (which is what the referenced answer was doing, not a BSON date) but that would be lengthy and inefficient.

What you really want to do is do your conversion in your code and pass in a MongoDate type.

Here's some round-trip code to consider:

date_default_timezone_set('UTC');

$dt = new DateTime("2014-02-01", new DateTimeZone('PST'));
echo var_dump( $dt );

echo $dt->getTimestamp(), "\n";

$mongo = new MongoDate( $dt->getTimestamp() );

echo var_dump( $mongo );

echo $mongo->sec, "\n";

$new = new DateTime( date('Y-m-d', $mongo->sec ), new DateTimeZone('PST'));

echo var_dump ($new);

So whether you are passing in values or working with results, just do the conversions in code. It's better that way.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
  • Clarification: Are you saying that I should pre-convert the time before recording it in MongoDb? – JC Lee Mar 10 '14 at 07:55
  • @komirad Yes. And back again if required. It is a best practice concept. Consider if your app was working with dates in another time zone than the one you are presently in. Then it would be necessary to take the local input and convert it to the timezone of another. This is why MongoDB uses UTC. It's best practice. Believe me, it's how we all do it. – Neil Lunn Mar 10 '14 at 07:58