Beginner in sql server

Question

Post reply

Beginner in sql server

YuChan

SSC Veteran

Points: 218

November 9, 2025 at 6:38 am

#4682734

Hello,

How to add a UNIX_TIMESTAMP at my publish_date please

CREATE TABLE [dbo].[latest_info](
[id] [int] IDENTITY(1,1) NOT NULL,
[type] [varchar](20) NULL,
[number] [varchar](255) NULL,
 [varchar](255) NULL,
[title] [varchar](255) NULL,
[text] [text] NULL,
[publish_date] [varchar](255) NULL,
[author] [varchar](50) NULL,
[icon] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[latest_info] ADD DEFAULT (CURRENT_TIMESTAMP) FOR [publish_date]
GO

This topic was modified 3 weeks, 6 days ago by YuChan.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2240 More actions · Answer 1

You're looking to set the DEFAULT value of the publish_date column to be the current Unix timestamp? If so the proper column type would be BIGINT. SQL Server’s native datetime types represent calendar datetimes and not “milliseconds since the Unix Epoch” so there’s no 1:1 equivalent to a Unix timestamp type. Storing datetimes, even Unix timestamp, as VARCHAR is not recommended. Maybe you could use DATEDIFF_BIG

select datediff_big(millisecond, '19700101', sysutcdatetime()) current_unix_ts;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

YuChan SSC Veteran Points: 218 More actions · Answer 2

Hello.

Thank you.

Can you add in exemple because i don't know how to use !

TY

Steve Collins SSCrazy Points: 2240 More actions · Answer 3

Assuming this is dev or the table is empty, DROP and recreate with the proper column type and default. Something like this

drop table if exists dbo.latest_info;
go
create table dbo.latest_info(
id                  int identity(1,1) constraint pk_latest_info primary key not null,
publish_date        bigint not null default datediff_big(millisecond, '19700101', sysutcdatetime()));
go

insert dbo.latest_info default values;
select * from dbo.latest_info;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können