How to convert date into timestamp in SQL query?

Question

I'm trying to migrate data from old_table to new_table, but in old table the date datatype is datetime and in new table the date datatype is int and is accepting timestamp value.

So how to convert the old date in sql query so that it get inserted in new table as timestamp?

INSERT INTO `new_table` (`id`, `user_id`, `doctor_id`, `message_id`, `type`, `is_message`, `is_note`, `doctor_initials`, `call_status`, `message`, `date_created`, `date_updated`, `day`)
  SELECT id, usid, drid, message_id, type, is_message, is_note, doctor, kall, message, datein, 123, ziua
  FROM `old_table`;

I want a function which convert old date to value to timestamp e.g in the above query CONVERT_INTO_TIMESTAMP(datein)

Any help will be highly appreciated.

New Table date_created field accepts values in unix timestamp such as : 1540642765

Thanks

Eugen Rieck · Accepted Answer · 2018-10-27 12:23:48Z

13

The function you are looking for is UNIX_TIMESTAMP(), e.g.

select UNIX_TIMESTAMP('1970-01-01 00:00:00');

gives 0 in the UTC timezone.

answered Oct 27, 2018 at 12:23

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhur Bhaiya · Accepted Answer · 2018-10-27 12:33:28Z

Try UNIX_TIMESTAMP() function:

INSERT INTO `new_table`
            (`id`,
             `user_id`,
             `doctor_id`,
             `message_id`,
             `type`,
             `is_message`,
             `is_note`,
             `doctor_initials`,
             `call_status`,
             `message`,
             `date_created`,
             `date_updated`,
             `day`)
SELECT id,
       usid,
       drid,
       message_id,
       type,
       is_message,
       is_note,
       doctor,
       kall,
       message,
       Unix_timestamp(datein),
       '123',
       ziua
FROM   `old_table`;

From Docs:

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. The date argument may be a DATE, DATETIME, or TIMESTAMP string, or a number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format. The server interprets date as a value in the current time zone and converts it to an internal value in UTC.

Gennadiy Bilyk · Accepted Answer · 2019-06-30 14:16:48Z

0

SELECT date_part('epoch', CURRENT_TIMESTAMP)::int;

answered Jun 30, 2019 at 14:16

Gennadiy Bilyk

516 bronze badges

Comments

Shawinder Jit Singh · Accepted Answer · 2023-02-01 16:29:04Z

0

since this question is also tagged for SQL people who came here for timestamp solution in SQL/MSSQL Query. can try below

SELECT CAST(DueDate AS TIMESTAMP) "NewDueDate"

where DueDate is column with having date like - YYYY-MM-DD HH:ii:ss . if your column is in type varchar then still above syntax will work fine.

answered Feb 1, 2023 at 16:29

Shawinder Jit Singh

1331 silver badge9 bronze badges

Comments

Akshay · Accepted Answer · 2024-08-01 09:36:22Z

0

For SQL Server

DateTime To TimeStamp

SELECT DATEDIFF(SECOND,'1970-01-01',GETUTCDATE());

TimeStamp To DateTime

SELECT DATEADD(SECOND, 1722504427,'1970-01-01');

answered Aug 1, 2024 at 9:36

Akshay

13 bronze badges

Collectives™ on Stack Overflow

How to convert date into timestamp in SQL query?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related