2

I have a content type with datetime field. When I save node, in database I have 2014-04-28 11:00:00 for instance. But when I edit the node i have 2014-04-28 13:00:00. I guess that it has something to do with timezones.

Now the problem is that i have lot of nodes and i have to get the time from database. I don't want to node_load or anything like that.

The question is - is it possible to extract 2014-04-28 13:00:00 datetime from database with MySQL query? Or any work around where I don't have to use node_load?

Any input will be appreciated :)

2

What version of Drupal is this?

It's against best practices to go directly to the database but if you really need to I don't see why you can't query the field's table. What problems are you having?

When entering data for a field, the data entered is assumed to be in the site's time zone. When the data is saved to the database, it is converted to UTC. When retrieved from the database, the data is converted to the Site's time zone for anonymous users or the User's time zone for logged in users when User-configurable time zones is enabled.

You may wanted to use format_date to convert your time. See: https://api.drupal.org/api/drupal/includes!common.inc/function/format_date/7

UPDATE:

I don't know what source you time is in, either a timestamp or formatted date/time. In your example you're using 2014-04-28 11:00:00 so lets convert that to a unix timestamp:

  $newdate = new DateTime('2014-04-28 11:00:00 UTC', new DateTimeZone(date_default_timezone_get()));
  $unixts = $newdate->getTimestamp();
  $siteDate = format_date($unixts, 'small', '', date_default_timezone_get());
| improve this answer | |
  • It is Drupal 7. I'm using db_select interface in a custom module. But it returns 13:00:00, i need to have it in site's time zone which will return 15:00:00, just like in form where i can edit it. When You wrote about converting. How can i do that? – Astaz3l Apr 28 '14 at 12:35
  • Is there a specific reason you don't want to use node_load? These things are handled for you at that point. See my updated answer for using format_date – Cravecode Apr 28 '14 at 12:43
  • I need to preprocess 10k nodes every 5 minutes, i think that node_load will not be such good option:) – Astaz3l Apr 28 '14 at 12:46
  • @Astaz3l ha! I agree then, better keep it simple and go directly to the field value. – Cravecode Apr 28 '14 at 12:48
  • 1
    Make sure your date/time has UTC appended to the end of it. See my update for another attempt – Cravecode Apr 28 '14 at 13:13
0

Gergely Lekli did a fantastic write-up on using views to avoid node_load() in Drupal 7.

By his calculations, using views was 6 fold faster.

With views I believe you will find it easier to output with the appropriate timezone as well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.