Where is the node created timestamp stored in the database?

Question

I need to create an SQL query that finds all nodes of a certain type created X days ago, so I can get their IDs. Due to the massive number of nodes in the database, I would prefer to use a direct SQL query, rather than entityQuery. I was warned entityQuery can be slow if working with large volumes of data.

Anyway, I need to find where is the node created timestamp stored in the database. In Drupal 7, it is stored directly in the node table, but not in Drupal 8.

Answer 1

The created date is stored in the node_field_data table as a UNIX timestamp in a cloumn called created.

Here's the code you would use to fetch the nodes that has been created X days ago, using a direct select query.

$day = 86400; // One day in seconds.
$days = 3; // The amount of days ago to fetch from.

$nids = \Drupal::database()->select('node_field_data', 'n')
  ->addField('n', 'nid')
  ->condition('n.created', \Drupal::time()->getCurrentTime() - $days * $day, '>')
  ->execute()
  ->fetchField();

$nodes = \Drupal::entityTypeManager()->getStorage('node')->loadMultiple($nids);

Make sure to inject the database and the entity_type.manager services (if possible) when implementing the code yourself.

Answer 2

If you go into your Drupal database you should find a table called 'node_field_data'. In this table there is a column labeled 'created'. That will be your Unix timestamp for when the node was created.