CURRENT TIMESTAMP in milliseconds

Question

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql  or others just out of curiosity    SELECT CURRENT TIMESTAMP -- gt  2012-03-08 20 12 06 032572   Is there anything like this   SELECT CURRENT MILLISEC -- gt  1331255526000   or the only alternative is to use the DATEDIFF from the era

User · Accepted Answer

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  Not tested PostgreSQL  but according to this site it should work  http   www raditha com postgres timestamp php  select round  date part   epoch   now

User · Answer

For everyone here  just listen   read the comments of Doin very good  The UNIX TIMESTAMP   function will  when a datatime-string is given  contact a local time  based on the timezone of the MySQL Connection or the server  to a unix timestamp  When in a different timezone and dealing with daylight savings  one hour per year  this will go wrong   For example  in the Netherlands  the last Sunday of October  a second after reaching 02 59 59 for the first time  the time will be set back to 02 00 00 again  When using the NOW    CURTIME   or SYSDATE  -functions from MySQL and passing it to the UNIX TIMESTAMP   function  the timestamps will be wrong for a whole our   For example  on Satudray 27th of October 2018  the time and timestamps went like this   Local time                           UTC Time                    Timestamp      Timestamp using MYSQL s UNIX TIMESTAMP NOW 4   ---------------------------------- --------------------------- -------------- ----------------------------------------------------- 2018-10-27 01 59 59 CET   02 00      2018-10-26 23 59 59 UTC     1540598399     1540598399 2018-10-27 02 00 00 CET   02 00      2018-10-27 00 00 00 UTC     1540598400     1540598400   1 second 2018-10-27 02 59 59 CET   02 00      2018-10-27 00 59 59 UTC     1540601999     1540601999  2018-10-27 03 00 00 CET   02 00      2018-10-27 01 00 00 UTC     1540602000     1540602000   1 second 2018-10-27 03 59 59 CET   02 00      2018-10-27 01 59 59 UTC     1540605599     1540605599 2018-10-27 04 00 00 CET   02 00      2018-10-27 02 00 00 UTC     1540605600     1540605600   1 second   But on Sunday 27th of October 2019  when we ve adjusted the clock one hour  Because the local time  doensn t include information whether it s  02 00 or  01 00  converting the time 02 00 00 the first time and the second time  both give the same timestamp  from the second 02 00 00  when using MYSQL s UNIX TIMESTAMP NOW 4   function  So  when checking the timestamps in the database  it did this   1   1   3601   1   1        1   1  -3599   1   1 etc   Local time                           UTC Time                    Timestamp      Timestamp using MYSQL s UNIX TIMESTAMP NOW 4   ---------------------------------- --------------------------- -------------- ----------------------------------------------------- 2019-10-27 01 59 59 CET   02 00      2019-10-26 23 59 59 UTC     1572134399     1572134399 2019-10-27 02 00 00 CET   02 00      2019-10-27 00 00 00 UTC     1572134400     1572138000   3601 seconds 2019-10-27 02 59 59 CET   02 00      2019-10-27 00 59 59 UTC     1572137999     1572141599 2019-10-27 02 00 00 CET   01 00      2019-10-27 01 00 00 UTC     1572138000     1572138000 - 3599 seconds 2019-10-27 02 59 59 CET   01 00      2019-10-27 01 59 59 UTC     1572141599     1572141599 2019-10-27 03 00 00 CET   01 00      2019-10-27 02 00 00 UTC     1572141600     1572141600   1 second   Relaying on the UNIX TIMESTAMP  -function from MySQL when converting local times  unfortunately  is very unreliable  Instead of using SELECT UNIX TIMESTAMP NOW 4    we re now using the code below  which seams to solve the issue   SELECT ROUND UNIX TIMESTAMP      MICROSECOND UTC TIME 6   0 000001   4

User · Answer

None of these responses really solve the problem in postgreSQL  i e       getting the unix timestamp of a date field in milliseconds   I had the same issue and tested the different previous responses without satisfying result   Finally  I found a really simple way  probably the simplest    SELECT  EXTRACT  EPOCH FROM  lt date column gt   timestamp   float 1000 as unix tms FROM  lt table gt    namely     We extract the pgSQL EPOCH  i e  unix timestamp in floatting seconds from our column casted in timestamp prudence  in some complexe queries  pgSQL could trow an error if this cast isn t explicit  See   then we cast it in float and multiply it by 1000 to get the value in milliseconds

User · Answer

In PostgreSQL we use this approach   SELECT round EXTRACT  EPOCH FROM now     float 1000

User · Answer

The main misunderstanding in MySQL with timestamps is that MySQL by default both returns and stores timestamps without a fractional part   SELECT current timestamp      gt  2018-01-18 12 05 34   which can be converted to seconds timestamp as  SELECT UNIX TIMESTAMP current timestamp      gt  1516272429   To add fractional part   SELECT current timestamp 3    gt  2018-01-18 12 05 58 983   which can be converted to microseconds timestamp as  SELECT CAST  1000 UNIX TIMESTAMP current timestamp 3   AS UNSIGNED INTEGER  ts   gt  1516272274786   There are few tricks with storing in tables  If your table was created like      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 fractional part within it       id  not fractional timestamp     1   2018-01-18 11 35 12   If you want to add fractional part into your table  you need to create your table in another way       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    that leads to required result       id  some data  fractional timestamp     1   8          2018-01-18 11 45 40 811   current timestamp   function is allowed to receive value up to 6  but I ve found out  at least in my installed MySQL 5 7 11 version on Windows  that fraction precision 6 leads to the same constant value of 3 digits at 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   That means that really usable timestamp precision of MySQL is platform-dependent    on Windows  3 on Linux  6

User · Answer

In MariaDB you can use  SELECT NOW 4     To get milisecs  See here  too

User · Answer

I felt the need to continue to refine  so in MySQL   Current timestamp in milliseconds   floor unix timestamp current timestamp 3     1000    Timestamp in milliseconds from given datetime 3    floor unix timestamp  2015-04-27 15 14 55 692     1000    Convert timestamp in milliseconds to datetime 3    from unixtime 1430146422456   1000    Convert datetime 3  to timestamp in milliseconds   floor unix timestamp  2015-04-27 14 53 42 456     1000

User · Answer

For mysql  SELECT  UNIX TIMESTAMP     1000  AS unix now in microseconds  --- 1600698677000

User · Answer

Poster is asking for an integer value of MS since Epoch  not a time or S since Epoch   For that  you need to use NOW 3  which gives you time in fractional seconds to 3 decimal places  ie MS precision   2020-02-13 16 30 18 236  Then UNIX TIMESTAMP NOW 3   to get the time to fractional seconds since epoc   1581611418 236  Finally  FLOOR UNIX TIMESTAMP NOW 3   1000  to get it to a nice round integer  for ms since epoc   1581611418236  Make it a MySQL Function   CREATE FUNCTION UNIX MS   RETURN BIGINT DETERMINISTIC BEGIN     RETURN FLOOR UNIX TIMESTAMP NOW 3   1000   END   Now run SELECT UNIX MS     Note  this was all copied by hand so if there are mistakes feel free to fix

User · Answer

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    This relies on the fact that NOW   always returns the same time throughout a query  it s possible that a plain UNIX TIMESTAMP   would work as well  I m not sure based on the documentation   It also requires MySQL    5 6 for the new precision argument for NOW   function  MariaDB works too

User · Answer

The correct way of extracting miliseconds from a timestamp value on PostgreSQL accordingly to current documentation is   SELECT date part  milliseconds   current timestamp    --OR  SELECT EXTRACT MILLISECONDS FROM current timestamp     with returns  The seconds field  including fractional parts  multiplied by 1000  Note that this includes full seconds

User · Answer

In mysql  it is possible to 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 mysql versions   Thank you to this page  http   rpbouman blogspot com es 2014 06 mysql-extracting-timstamp-and-mac html

User · Answer

In Mysql 5 7  you can execute   select current timestamp 6    for more details  https   dev mysql com doc refman 5 7 en fractional-seconds html

User · Answer

Do as follows for milliseconds   select round date format CURTIME 3     f   1000    You can get microseconds by the following   select date format CURTIME 6     f

User · Answer

Postgres  SELECT  extract epoch from now    1000   bigint

User · Answer

Easiest way I found to receive current time in milliseconds in MySql   SELECT  UNIX TIMESTAMP NOW 3     1000    Since MySql 5 6

User · Answer

For MySQL  5 6   you can do this   SELECT ROUND UNIX TIMESTAMP CURTIME 4     1000    Which will return  e g     1420998416685 --milliseconds

User · Answer

I faced the same issue recently and I created a small github project that contains a new mysql function UNIX TIMESTAMP MS   that returns the current timestamp in milliseconds   Also you can do the following    SELECT UNIX TIMESTAMP MS NOW 3   or SELECT UNIX TIMESTAMP MS DateTimeField   The project is located here   https   github com silviucpp unix timestamp ms  To compile you need to Just run make compile in the project root   Then you need to only copy the shared library in the  usr lib mysql plugin   or whatever the plugin folder is on your machine    After this just open a mysql console and run    CREATE FUNCTION UNIX TIMESTAMP MS RETURNS INT SONAME  unix timestamp ms so    I hope this will help  Silviu

User · Answer

In PostgreSQL you can use     SELECT extract epoch from now       on MySQL    SELECT unix timestamp now

User · Answer

Use   Select curtime 4     This will give you milliseconds

[mysql] CURRENT_TIMESTAMP in milliseconds

Examples related to mysql

Examples related to sql

Examples related to postgresql

Examples related to datetime

Examples related to timestamp