Setting a 14-digit current timestamp

Forum Moderators: open

Message Too Old, No Replies

Setting a 14-digit current timestamp

csdude55

3:49 am on Feb 15, 2019 (gmt 0)

In MySQL, I format my timestamps as a 14-digit number. So, this:

2019-02-14 22:05:14

becomes:

20190214220514

I have a query that looks like this:

SET @var_old_date = 20190214134909;
SET @var_new_date = 20190214161409;

INSERT INTO table_1
 SELECT @var_new_date, colB, colC FROM table_2
  WHERE postdate=@var_old_date;

Is there a way that I can set @var_new_date automatically, though, without plugging in my own number?

I've tried using both of these:

NOW() + 0
SELECT CONCAT(CURDATE() + 0, CURTIME() + 0)

but both add ".000000" microseconds to the end.

csdude55

5:45 am on Feb 15, 2019 (gmt 0)

Update, I found two options that seem to work well unless you guys think there's a better solution:

SET @var_new_date = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');

SET @var_new_date = SUBSTRING(NOW()+0, 1, 14);

Both seemed to take about the same amount of time to run.

topr8

9:46 pm on Feb 15, 2019 (gmt 0)

is there any reason why you are not using the built in timestamp field?

csdude55

11:55 pm on Feb 15, 2019 (gmt 0)

Yes and no... when I first built the system I was using flat text, and just set the timestamp as a 14 digit number. Then when I moved to MySQL, I really didn't know enough about it and just kept the original format.

Now I have tens of millions of rows and dozens of programs written around it, and it would be a lot of trouble to change everything.

Are there any major advantage to moving everything from BIGINT to DATETIME? They're both 8 bytes of storage, right?

topr8

7:47 pm on Feb 17, 2019 (gmt 0)

that makes sense to do it the way you are in that case!

robzilla

8:09 pm on Feb 17, 2019 (gmt 0)

There's a bunch of MySQL functions [dev.mysql.com] you can use with DATETIME that you cannot use with BIGINT without parsing the values as a date first (expensive). Of course, you may not have a need for any of that. However, you're probably now also having to specially parse the dates in PHP before you can use them as such.