CURRENT_TIMESTAMP in milliseconds

Question

Is there a way to get milliseconds from a timestamp in MySql or PostgreSql (or others just out of curiosity)?

 SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 

Is there something like this:

 SELECT CURRENT_MILLISEC --> 1331255526000 

or is the only alternative to using era DATEDIFF ?

Answer 1

To get the Unix timestamp in seconds in MySQL:

 select UNIX_TIMESTAMP(); 

Details: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

PostgreSQL has not been tested, but according to this site it should work: http://www.raditha.com/postgres/timestamp.php

 select round( date_part( 'epoch', now() ) ); 

Answer 2

For MySQL (5.6+) you can do this:

 SELECT ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000) 

What will return (for example):

 1420998416685 --milliseconds 

Answer 3

In mysql, you can use the uuid function to extract milliseconds.

 select conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)) ,16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills from (select uuid() uid) as alias; 

Result:

 +---------------+ | current_mills | +---------------+ | 1410954031133 | +---------------+ 

It also works in older versions of mysql!

Thank you for this page: http://rpbouman.blogspot.com.es/2014/06/mysql-extracting-timstamp-and-mac.html

Answer 4

The correct way to extract milliseconds from a timestamp value in PostgreSQL according to the current documentation :

 SELECT date_part('milliseconds', current_timestamp); --OR SELECT EXTRACT(MILLISECONDS FROM current_timestamp); 

return: field of seconds, including fractional parts, multiplied by 1000. Note that this includes full seconds.

Answer 5

Using:

 Select curtime(4); 

This will give you milliseconds.

Answer 6

In Mysql 5. 7+ you can execute

 select current_timestamp(6) 

More details

https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

Answer 7

In PostgreSQL you can use:

 SELECT extract(epoch from now()); 

in MySQL:

 SELECT unix_timestamp(now()); 

Answer 8

None of these answers really solve the problem in postgreSQL , i.e.

getting unix timestamp date fields in milliseconds

I had the same problem and tested different previous answers without satisfying the result.

Finally, I found a really easy way, perhaps the easiest:

 SELECT (EXTRACT (EPOCH FROM <date_column>::timestamp)::float*1000 as unix_tms FROM <table> 

namely:

• We extract pgSQL EPOCH, i.e. unix timestamp, in the floating point fields from our column, abandoned at the base of the timestamp (in some complex queries, pgSQL may cause an error if this cast is not explicit).
• then we will use it in a float and multiply it by 1000 to get the value in milliseconds

Answer 9

Here's an expression that works for MariaDB and MySQL> = 5.6:

 SELECT (UNIX_TIMESTAMP(NOW()) * 1000000 + MICROSECOND(NOW(6))) AS unix_now_in_microseconds; 

It depends on the fact that NOW () always returns the same time during the request ; it is possible that a simple UNIX_TIMESTAMP() will work, I'm not sure based on the documentation . It also requires MySQL> = 5.6 for a new precision argument for the NOW() function NOW() MariaDB also works).

Answer 10

The easiest way to find the current time in milliseconds in MySql:

 SELECT (UNIX_TIMESTAMP(NOW(3)) * 1000) 

Since MySql 5.6.

Answer 11

The main misunderstanding in MySQL with timestamps is that by default MySQL returns and saves timestamps without a fraction .

 SELECT current_timestamp() => 2018-01-18 12:05:34 

which can be converted to seconds mark in seconds

 SELECT UNIX_TIMESTAMP(current_timestamp()) => 1516272429 

To add a fractional part:

 SELECT current_timestamp(3) => 2018-01-18 12:05:58.983 

which can be converted to a timestamp in microseconds, like

 SELECT CAST( 1000*UNIX_TIMESTAMP(current_timestamp(3)) AS UNSIGNED INTEGER) ts => 1516272274786 

There are several tricks with table storage. If your table was created as

  CREATE TABLE 'ts_test_table' ( 'id' int(1) NOT NULL, 'not_fractional_timestamp' timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

than MySQL will not store the fractional part inside it:

  id, not_fractional_timestamp 1, 2018-01-18 11:35:12 

If you want to add a fractional part to your table, you need to create your table differently:

  CREATE TABLE 'ts_test_table2' ( 'id' int(1) NOT NULL, 'some_data' varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, 'fractional_timestamp' timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

which leads to the desired result:

  id, some_data, fractional_timestamp 1, 8, 2018-01-18 11:45:40.811 

The function current_timestamp () can get a value up to 6, but I found (at least in my installed version of MySQL 5.7.11) that the accuracy of fraction 6 leads to the same constant value of 3 digits in the tail, in my case 688

  id, some_data, fractional_timestamp 1, 2, 2018-01-18 12:01:54.167688 2, 4, 2018-01-18 12:01:58.893688 

This means that the actual usable precision in MySQL is 3.

Answer 12

I felt the need to keep improving, so in MySQL:

Current timestamp in milliseconds:

 floor(unix_timestamp(current_timestamp(3)) * 1000) 

Timestamp in milliseconds from a given time (3):

 floor(unix_timestamp("2015-04-27 15:14:55.692") * 1000) 

Convert timestamp in milliseconds to datetime (3):

 from_unixtime(1430146422456 / 1000) 

Convert date and time (3) to timestamp in milliseconds:

 floor(unix_timestamp("2015-04-27 14:53:42.456") * 1000) 

Answer 13

In MariaDB you can use

 SELECT NOW(4); 

Get milisecs. See here .

Answer 14

In PostgreSQL, we use this approach:

 SELECT round(EXTRACT (EPOCH FROM now())::float*1000) 

Answer 15

I recently ran into the same problem and created a small github project containing a new mysql function UNIX_TIMESTAMP_MS() that returns the current timestamp in milliseconds.

You can also do the following:

SELECT UNIX_TIMESTAMP_MS(NOW(3)) or SELECT UNIX_TIMESTAMP_MS(DateTimeField)

The project is here: https://github.com/silviucpp/unix_timestamp_ms

To compile you just need to run make compile in the root of the project.

Then you only need to copy the shared library to /usr/lib/mysql/plugin/ (or any other plugins folder on your computer.)

After that, just open the mysql console and run:

CREATE FUNCTION UNIX_TIMESTAMP_MS RETURNS INT SONAME 'unix_timestamp_ms.so';

Hope this helps, Silviu

Answer 16

In MySQL:

  SELECT UNIX_TIMESTAMP() * 1000;