datetime, datetimetz, datetimenotz - when to use each?

[god_prophet]

I need to create a XML that has a column called: date_creation, the values are of the form: 2023-05-12 18:09:07. Which of the type of data stored in the element should I use here: 

<attribute label="Fec. Registro Notificacion Preferencia Cliente" name="date_creation"
type="datetime, datetimetz, datetimenotz???"/>

We're based in Perú, GTM -5.


https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/schema-r...

[Parvesh_Parmar]

Hello @god_prophet,

You need to create a field like below to store date time.

           <attribute name="date" label="Subscription date" type="date"/>

Value of type should be date.

Then it will create a field in the backend with date time automatically.

It mean you can store the date and time both there.

You don’t need to define anything related to time zone in the schema.

Time zone is defined on instance level or you can select it on workflow properties also if your server is in different time zone then your.

Please check the below link for more details.

https://experienceleague.adobe.com/docs/campaign-classic/using/installing-campaign-classic/install-c...

https://experienceleague.adobe.com/docs/campaign-classic/using/automating-with-workflows/advanced-ma...

Thanks,

Parvesh

[ParthaSarathy · Accepted solution]

Hi @god_prophet ,

In postgreSQL data type of datetime and datetimetz serves same purpose. It will store as TIMESTAMPTZ datatype in Database.

Whereas when you define datatype as datetimenotz, in database it will store as TIMESTAMP datatype.

(However, when you see the data from schema's data tab in adobe campaign UI, It will displays the same for both datatypes)

Consider, when you create 3 attributes as below in adobe campaign,

<attribute label="Datetime" name="datetimeField" type="datetime"/>
<attribute label="Datetimetz" name="datetimetzField" type="datetimetz"/>
<attribute label="Datetimenotz" name="datetimenotzField" type="datetimenotz"/>

And when you update the database structure, you can notice the data type generated in database,

     ADD COLUMN tsDatetimeField TIMESTAMPTZ,
     ADD COLUMN tsDatetimetzField TIMESTAMPTZ,
     ADD COLUMN tsDatetimenotzField TIMESTAMP;

And difference between TIMESTAMP and TIMESTAMPTZ is,

• timestamp: a timestamp without timezone one.
• timestamptz: timestamp with a timezone.

The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.

The timestamptz datatype is the timestamp with the time zone. The timestamptz datatype is a time zone-aware date and time data type.

PostgreSQL stores the timestamptz in UTC value.

• When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
• When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection.

Reference: Understanding PostgreSQL Timestamp Data Types

So considering your use case, you can decide which datatype need to be used!

~  Parthasarathy S

[god_prophet]

Hi Parthasarathy, 

How does the election of "date" instead of "datetime" in the XML schema relates to the Data type we have to choose in the "Data loading activity" ? 

Does it get affected if I have chosen "date" and not "datetime" in the XML schema , but then "date + time" in the "Data loading activity"? 

[ParthaSarathy]

@god_prophet , basically when you give type as 'date' in XML schema, the data will get stored as 28/10/1995

And when you create data type as 'datetime' in XML schema, the data will get stored as 28/10/1995 15:04:49

So, If you have a 'date' field in XML Schema, and in Data loading activity if you select 'data+time', even though if your input file has data as "28/10/1995 15:04:49", but when uploading this record in database, it will get stored as 28/10/1995 in your 'date' field attribute.