How do I recover the current date of a Mysql database in Arduino?

Question

I’m making database connection mysql using the Arduino next to esp8266, wanted to know how to take current date store in a variable to then send to the bank. I’m in trouble just to get the current date.

Answer 1

The MySQL has a date and time call manipulation function UNIX_TIMESTAMP(), which in turn is able to return the date and time of the database represented by the amount of seconds that have passed since the Unix age (From 00:00 on 1 January 1970):

SELECT UNIX_TIMESTAMP();

In C, the guy time_t, defined by the standard library time.h has the purpose of storing, the time interval in seconds, from the Unix age.

Putting the two together, you could implement a function more or less like this:

#include <time.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

time_t obterDataHoraBD( MySQL_Connection * pconn )
{
  row_values * row = NULL;
  time_t t = 0;

  MySQL_Cursor * cur = new MySQL_Cursor( pconn );
  cur->execute( "SELECT UNIX_TIMESTAMP();" );
  row = cur->get_next_row();
  t = atol( row->values[0] );
  delete cur;

  return t;
}

To convert a time_t for something humanly readable, you can use the functions localtime() and gmtime() that return a struct tm:

struct tm                                                                                                
{                                                                                                   
   int tm_sec;           /* Seconds. [0-60] (1 leap second) */      
   int tm_min;           /* Minutes. [0-59] */                            
   int tm_hour;          /* Hours.   [0-23] */                       
   int tm_mday;          /* Day.     [1-31] */             
   int tm_mon;           /* Month.   [0-11] */                
   int tm_year;          /* Year - 1900.  */                       
   int tm_wday;          /* Day of week. [0-6] */                 
   int tm_yday;          /* Days in year.[0-365] */                        
   int tm_isdst;         /* DST.     [-1/0/1]*/         
}

You can also use the function ctime(), who receives a time_t and returns a pointer to a string containing the date and time in the following format:

"Thu Jun 21 08:27:33 2018\n"

Follow a demonstrative code applying the ideas:

#include <time.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

EthernetClient client;
MySQL_Connection conn( (Client*) &client );

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress local_ip( 192, 168, 1, 123 );

IPAddress server_addr( 192, 168, 1, 1 );
int port = 3306;

char user[] = "nome_usaurio";
char password[] = "senha";

void setup()
{
  Serial.begin(9600);
  while(!Serial);

  Ethernet.begin( mac_addr, local_ip );

  conn.connect( server_addr, port, user, password );
}

time_t obterDataHoraBD( MySQL_Connection * pconn )
{
  row_values * row = NULL;
  time_t t = 0;

  MySQL_Cursor * cur = new MySQL_Cursor( pconn );
  cur->execute( "SELECT UNIX_TIMESTAMP(NOW());" );
  row = cur->get_next_row();
  t = atol( row->values[0] );
  delete cur;

  return t;
}

void loop()
{
    time_t datahora = obterDataHoraBD( &conn );

    Serial.print( "UNIX Epoch= " );
    Serial.println( datahora );

    Serial.write( "Data/Hora= " );
    Serial.println( ctime(datahora) );

    delay(5000);
}