0

UPDATE: With a more articulate example.

there are create queries mentioned in below link written in MySQL syntax, can you create them the similar way in postgres wrt timestamp values:

https://www.techbeamers.com/sql-query-questions-answers-for-practice/

You would notice all the timestamp values of WORKER table in this link are in format DD-MM-YY HH.MM.SS and not DD-MM-YY HH:MM:SS

What I've tried

While creating a table using Mysql

I could do below using DATETIME:

 CREATE TABLE Worker (
    WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT(15),
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);
    
    INSERT INTO Worker
        (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) 
        VALUES
        (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR')

This returns no error in MYSQL

However, when I use TIMESTAMP (counterpart) in Postgres:

   CREATE TABLE Worker(
    WORKER_ID SERIAL PRIMARY KEY,
    FIRST_NAME VARCHAR(250) NOT NULL,
    LAST_NAME VARCHAR(250),
    SALARY INT,
    JOINING_DATE TIMESTAMP, 
    DEPARTMENT VARCHAR(15)
);

INSERT INTO Worker
        (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) 
        VALUES
        (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR')

I get an error: Incorrect timestamp syntax

So for now, I am using below logic

I create JOINING_DATE VARCHAR(20), i.e. create joining date in varchar, then alter the table:

CREATE TABLE Worker(
WORKER_ID SERIAL PRIMARY KEY,
FIRST_NAME VARCHAR(250) NOT NULL,
LAST_NAME VARCHAR(250),
SALARY INT,
JOINING_DATE VARCHAR(250),  
DEPARTMENT VARCHAR(15)
);

    INSERT INTO Worker
            (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) 
            VALUES
            (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
            (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
            (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
            (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
            (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
            (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
            (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
            (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');
    
    ALTER TABLE worker 
      ALTER COLUMN joining_date 
       TYPE TIMESTAMP WITH TIME ZONE 
         USING to_timestamp(joining_date, 'DD-MM-YYYY HH24:MI:SS');

Is there a better way to do this just like in SQL, can't postgres SQL read '14-02-20 09.00.00' without errors?

Priyal Mangla
  • 97
  • 8
  • Yes, MySQL is very tolerant with invalid input and often prefers to corrupt your data over throwing an error message. As human being, I cannot tell what a date like `12-10-09 09.00.00` means. Can't you just feed your query with an unambiguous date format? Otherwise, you'll need to specify the format as you already do in the `to_timestamp()` part. – Álvaro González Jun 29 '21 at 08:49
  • '2014-02-20 09:00:00' or '2020-02-14 09:00:00' not sure which one you mean. – Serg Jun 29 '21 at 08:55
  • @ÁlvaroGonzález *Yes, MySQL is very tolerant with invalid input and often prefers to corrupt your data over throwing an error message.* No, this is SQL Server Mode-dependent. In strict mode this will cause an error. If not then warning instead of error is generated and NULL value produces. – Akina Jun 29 '21 at 09:10
  • 1
    There are well-defined **standard** formats for dates and date times. Use them! `'2020-02-14 09:00:00'`. Don't expect databases to understand every bespoke formats when this problem has already been solved. – Gordon Linoff Jun 29 '21 at 10:26
  • I completely understand and that absolutely makes sense to make sure entering proper date formats itself. However, there are cases where you copy create queries from somewhere else and work on those tables. In that case I cannot or would not go and change the format for each date. Try creating tables mentioned in below link on postgres, would make my question more valid for you: https://www.techbeamers.com/sql-query-questions-answers-for-practice/ – Priyal Mangla Jul 06 '21 at 20:31
  • Editing my question with this link too – Priyal Mangla Jul 06 '21 at 20:33

0 Answers0