Compare date bash script

rdie77 · December 10, 2019, 4:56am

I all
I have written a bash script for compare two date. One of those is a result of query, and another is current date.
I have a problem with the format, because the first is 09/12/19 18:50:30 but for having this result I have to do

 d1DB=$(date -d "$valData" +'%m/%d/%y %T')

and the second is 09/12/19 18:52:05

currenttime=$(date +'%d/%m/%y %T')

I want inform you that the first date is taken as string from DB, and after I convert it in date with parameter +'%m/%d/%y' and NOT +'%m/%d/%y' because I have month and day reverse

How I can resolve this problem of format?
How I can compare two date, so I can verify when the diff is more or less of 3 minutes?

Thanks a lot

Neo · December 10, 2019, 5:38am

Personally, I always convert formatted time strings to a unix timestamp before doing time operations, and then covert the resulting time stamp back to a formatted time string if I need one.

Also, in all the databases I use on a regular basis, the dates and times are all stored as unix time stamps in the tables.

As an example, last week I wrote some code which estimated the load on the server and modified the DB queries based on the load and then calculated the estimated time to completion of that project in the log files, updated every minute. All the calculation were based on unix time stamp except the final log file entry, which I converted to my local time format for easy reading.

There are lot of people who will write code to do time operations on formatted time strings; but this is "a kludge" in my view, as time operations should be performed and stored as a unix time stamp and when a human wants to read the time, we then covert the time stamp to a local format based on the time zone of the user.

Also, let's say that you are in Brazil and your friend is in Japan. You want to do the same task at the same time (on a computer). It is best to specify the exact time as a unix time stamp, so you both will use the same time; and if you want to know the "formatted time" you can covert that time to a time string appropriate for your time zone.

Anyway.... that is my suggestion. That is what I always do...... all operations (processing) in unix timestamps.

YMMV

rbatte1 · December 10, 2019, 7:42am

Your two parameters (which I have wrapped in ICODE tags for you) appear to be the same to me. Notwithstanding, I'm wondering if the table column in question is a string or a date. The output of DESC your_table_name ; will should you what it is defined as. If it is stored as a date field, then we can probably adjust the SELECT statement.

Can you show us your table structure, the query and some sample output that we need to logically compare? The way to do it will be to convert it to Unix time (number of seconds since the Epoch, 1/1/1970) as Neo says, and then it is a simple subtraction.

Thanks, in advance,
Robin

rdie77 · December 10, 2019, 8:25am

Thanks a lot for your suggest.
My table DESC is:

MariaDB [RaspyDB]> DESC TemperatureLOG;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| TimeLog | datetime    | NO   | MUL | NULL    |       |

My complete code is:

#!/bin/bash
source /var/www/script/QUERY/configLogin.cnf

DB_USER=$user >&2
DB=$DB >&2
DB_PASS=$pwd >&2
DB_IP=$IP >&2



query="select DATE_FORMAT(TimeLog, '%d/%m/%y %H:%i:%s') FROM TemperatureLOG order by TimeLog DESC LIMIT 1;"

tempVal=$(mysql -u "$DB_USER" --password="$DB_PASS" --host="$DB_IP" --database="$DB" --execute="$query" | awk 'NR==2{print $1,$2}')
echo "string  DB:$tempVal="


dataDB_tmp=${tempVal%% *}
echo "data_tmp DB:$dataDB_tmp"

d1DB=$(date -d "$tempVal" +'%m/%d/%y %T')
echo "data DB:$d1DB"

dataDB=$(date -d "$dataDB_tmp" +"%d/%m/%y");
echo "data DB:$dataDB"

timeDB=${tempVal##* }
echo "time DB:$timeDB"


currenttime=$(date +'%d/%m/%y %T')
echo "current time:$currenttime"

curDate=$(date +'%d/%m/%y')
echo "current data:$curDate"

curTime=$(date +'%T')
echo "current time:$curTime"
if [[ $D1db > $currenttime ]];
then
        echo "db minore current"
else
        echo "current maggiore db"
fi


echo $(( $currenttime - $D1db ))

val1=2013-12-31T00:00:00
val2=2014-11-19T15:40:30
if [[ $val1 < $val2 ]];
then
        echo $?
else
        echo $?
fi

echo $(( $val2 - $val1 )/(60*60*24))

below same test of query

MariaDB [RaspyDB]> select DATE_FORMAT(TimeLog, '%d/%m/%y %H:%i:%s') FROM TemperatureLOG order by TimeLog DESC LIMIT 1;
+-------------------------------------------+
| DATE_FORMAT(TimeLog, '%d/%m/%y %H:%i:%s') |
+-------------------------------------------+
| 10/12/19 14:21:40                         |
+-------------------------------------------+
1 row in set (0.01 sec)

MariaDB [RaspyDB]> select TimeLog FROM TemperatureLOG order by TimeLog DESC LIMIT 1;
+---------------------+
| TimeLog             |
+---------------------+
| 2019-12-10 14:23:41 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [RaspyDB]>

Neo · December 10, 2019, 8:40am

Yeah, that is your "problem"

You store the date and time as a formatted datetime string.

This creates a mess.

It is best to store all your dates (as I said) as an integer(11) not a string but since you are storing as a datetime string, that's OK... you can deal with this easily:

The first thing you need to do is to convert that datetime string to a unix timestamp, for example (like this):

query="select UNIX_TIMESTAMP( 'YOUR DATETIME STRING HERE') FROM TemperatureLOG order by TimeLog DESC LIMIT 1;"

I did not test it, but you get the idea. UNIX_TIMESTAMP() will take a formatted string and convert it to unix time.

MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIX_TIMESTAMP().

When this function is used with a date argument, it returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.

The argument may be a DATE, DATETIME,TIMESTAMP or a number in YYYYMMDD or YYMMDD .

Note: Since UNIX_TIMESTAMP() works on the current datetime , your output may vary from the output shown.

Syntax:
UNIX_TIMESTAMP(); UNIX_TIMESTAMP(date); UNIX_TIMESTAMP(datetime);

Neo · December 10, 2019, 9:02am

Note, you can probably just do something like this:

query="select UNIX_TIMESTAMP(TimeLog) as mytime FROM TemperatureLOG order by mytime DESC LIMIT 1;"

rdie77 · December 10, 2019, 10:54am

Thanks a lot
With your suggest I have this result

MariaDB [RaspyDB]> select UNIX_TIMESTAMP(TimeLog) as mytime FROM TemperatureLOG order by mytime DESC LIMIT 1;
+------------+
| mytime     |
+------------+
| 1575991329 |

Now I have to convert this value to date, correct? and after compare with current time

Neo · December 10, 2019, 11:18am

No

You do not do any calculations, operations, math or otherwise in any format other than in unix time.

You should never do any calculations in "date" (string) format or compare strings.

Datetime strings are not for calculations, they are only to make it easy for humans to read.

Not sure why you are seemingly not understanding this basic concept.

You have never worked with unix time before??

rdie77 · December 10, 2019, 11:28am

no, I didn't

Neo · December 10, 2019, 8:14pm

From mobile ...

So it is really simple.

Formatted time strings are for humans.

Unix time is for computers.

Do all your time operations on a computer with time represented as unix time.

If you need to show the results to a human, you can covert unix time to a time string based on the locale of the human.

If you have formatted time strings and you want to do computer operations on them, first convert those formatted time strings to unix time.

It is really very simple.

Hope this helps.